## 5 replies

Last post Jan 27, 2016 04:05 AM by sophia_asp

• sophia_asp

Member

43 Points

598 Posts

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

• oned_gk

All-Star

52222 Points

15523 Posts

### Re: Filter already connected users

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

Programming to simplify, don't look for difficult way
• TechView

Participant

1779 Points

844 Posts

### Re: Filter already connected users

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.

Helping others is to provide help to yourself. Mark as answer, if the solution helps.

TechView
• sophia_asp

Member

43 Points

598 Posts

### Re: Filter already connected users

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.

• sophia_asp

Member

43 Points

598 Posts

### Re: Filter already connected users

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

• sophia_asp

Member

43 Points

598 Posts