locked
Filter already connected users RRS feed

  • Question

  • User584139459 posted

    Hi all,

    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
    Id
    Name
    Age
    MinAge
    MaxAge

    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

    e.g.
    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


    ConnectedUsers
    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.

    Thanks
    Sophia

    Monday, January 25, 2016 7:44 PM

Answers

  • User-1716253493 posted

    Try this

    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)

    below

    Users.id < Users_1.id

    To avoid A connected to B, B connected to A

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 26, 2016 1:40 AM

All replies

  • User-1716253493 posted

    Try this

    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)

    below

    Users.id < Users_1.id

    To avoid A connected to B, B connected to A

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 26, 2016 1:40 AM
  • User-698989805 posted

    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.

    Tuesday, January 26, 2016 10:34 AM
  • User584139459 posted

    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.


    Tuesday, January 26, 2016 6:08 PM
  • User584139459 posted

    Hi,

    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.

    Thanks
    Sophia

    Tuesday, January 26, 2016 6:35 PM
  • User584139459 posted

    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.

    Wednesday, January 27, 2016 4:05 AM