Last post Jan 27, 2016 04:05 AM by sophia_asp
Jan 25, 2016 07:44 PM|sophia_asp|LINK
I have 2 tables Users and ConnectedUsers
I need to add users in ConnectedUsers table who are already not connected with each other.
Users table has following fields
ConnectedUsers has fields Id, UserId, ConnectedUserId
Now please tell me how do I compare users with one another and put then in ConnectedUsers in efficient way. (Records will grow rapidly in future, so need to take care of performance of SQL query)
So condition of Adding users in ConnectedUsers is
1. Users should not be Connected already.
2. Every user's min age and max age has to be checked when comparing them
Consider records in Users table like
Id Name Age MinAge MaxAge
1 A 22 23 26
2 B 23 25 27
3 C 24 21 23
4 D 23 22 24
Id UserId ConnectedUserId
1 1 3
So I need to compare each user's min and max age
If we look at above data then user A age is 22 and his preference is min age = 23 and max age = 26
If I compare A with B then we can think these as ConnectedUsers but on the other hand B wants that user being connected should be of min age = 25 and max age = 27
So we can not put them in ConnectedUsers table.
Now if we compare A with C, then both satisfy each others' min and max age criteria so we can put them in ConnectedUsers but those are already connected, so again we can not put them in ConnectedUsers table.
Now if we compare A with D, then both satisfy each others' min and max age criteria so we can put them in ConnectedUsers and also they are not already connected, so we can put them in ConnectedUsers table.
Please tell me how do I compare users data and put in another table, also I want that search should be in such a way that query should be move forward, meaning if I already have compared A with B then there is no point in comparing B with A again
so it will compare
A with B, C, D.
B with C, D
C with D
Any help is much appreciated.
Jan 26, 2016 01:40 AM|oned_gk|LINK
SELECT ConnectedUsers_1.UserID, ConnectedUsers_1.ConnectedUserID
FROM (SELECT Users.id AS UserID, Users_1.id AS ConnectedUserID
FROM Users INNER JOIN
Users AS Users_1 ON Users.id <> Users_1.id AND Users.id < Users_1.id
WHERE (Users_1.age BETWEEN Users.minage AND Users.maxage)) AS ConnectedUsers_1 LEFT OUTER JOIN
ConnectedUsers ON ConnectedUsers_1.UserID = ConnectedUsers.userid AND ConnectedUsers_1.ConnectedUserID = ConnectedUsers.connecteduerid
WHERE (ConnectedUsers.userid IS NULL)
Users.id < Users_1.id
To avoid A connected to B, B connected to A
Jan 26, 2016 10:34 AM|TechView|LINK
It seems like you are trying to add the user profiles, that matches the min and max age criteria. As the age limit is defined, you should only check for the users that has the similar age range. Following should do to check the age differences:
SELECT m.UserID, m.UserName FROM Users m WHERE m.MinAge > 22 AND m.MaxAge <= 26
The above is a simple example to compare. Now I've min and max ages directly to the query. I think, you are maintaining cookie or session for the user login. So when the user is logged in to the system, then keep the MinAge and MaxAge in a session variable
for that specific user:
Convert.ToInt32(SESSION["MinAge"]) = AgeMin;
Convert.ToInt32(SESSION["MaxAge"]) = AgeMax;
Then pass this sessions to the pages where you want to see other users profile:
int m = Convert.ToInt32(SESSION["LoggedInUser"]);
SELECT m.UserID, m.UserName FROM Users m INNER JOIN ConnectedUsers k on k.UserId = m.UserID WHERE (m.MinAge > AgeMin AND m.MaxAge <= AgeMax) AND k.UserId != m AND k.ConnectedUserID != m.UserID
Now the above is partially dynamic that check age limits and whether a user is not connected to the logged in user. May be this could not be the proper solution but will guide to make it complete. Hope this helps.
Jan 26, 2016 06:08 PM|sophia_asp|LINK
Thank you both of you.
I have tried Oned_gk's solution and it seems working fine.
I want to know that how efficient this query would be in case of large database.
I would need to enhance this query with more conditions later, and I hope you would be helping in that as well :)
Thanks again both of you to help out in this.
Jan 26, 2016 06:35 PM|sophia_asp|LINK
I am in need to enhance the above scenario.
There is one more filter which I did not mention earlier to keep example as short as possible.
Filter is Min Height and Max Height
So user table is
Id, Name, Age, Height, MinAge, MaxAge, MinHeight, MaxHeight
as you know that there may come many results for user but I want to limit results for every user to max 10 records at a time.
I have two ways in mind
1. Find results without any filter (Min Max Age and Min Max Height) and then start adding filters one by one to shrink results count in case records come more than 10 at first.
2. Find results with every filter applied at first and then if results are less than 10 at first attempt then start removing filters one by one to expand result counts.
I would need to do this for every user and tables involved are same
Users & UsersConnected
Please tell me which approach will be fine and performance oriented and how I can write such query.
Will be very thankful to you guys.
Jan 27, 2016 04:05 AM|sophia_asp|LINK
Sorry to un mark Oned's answer, I will mark that as answer again.
I just wanted to have you a look on the last post (otherwise guys do not see marked answers as these are considered as solved and it is related to first post)
so that it is complete and I will mark both as answer.