CODEWITHSUNDEEP
mysqlsql
Mickey Kawick
Mickey Kawick

Reputation: 187

SQL find records based on two columns

Given a users table with primary key of id, I have a foreign table called friends which only has two fields (userid1, userid2). This allows us to create any kind of relationship between different users (one to many, one to one, etc). A user can appear in either column and both columns are equal. IOW, a single entry per relationship.

How can I pull all of the friends that a given user id has. Say Jonny, has 3 friends and his user id is 16... should my sql query look like this? 

SELECT * 
FROM   db.users 
       JOIN db.friends 
         ON db.users.id = db.friends.userid1 
            AND db.users.id = 16

Hopefully, this is clear. Also, if possible, can I exclude Jonny from the result set?

This query, as listed gies me the following:

id      name    uuid                       birthday        userid1  userid2
16  jonny   ABCDEFGHIJKLMNOP    1967-04-27 01:00:00     1         2
16  jonny   ABCDEFGHIJKLMNOP    1967-04-27 01:00:00     1         3

This is pretty close, except I want his friends, not jonny

Thanks guys, so I got it to work thanks to you. Here is the final working query.

SELECT * 
FROM db.users
WHERE db.users.id IN
(
  SELECT db.friends.userid2 as id FROM db.friends WHERE db.friends.userid1 = 16
    union
  SELECT db.friends.userid1 as id FROM db.friends WHERE db.friends.userid2 = 16
)

which gives me:

id      name    uuid                       birthday 
2   robin   ABCDEFGHIJKLMNOP    1967-04-27 01:00:00
3   gary    ABCDEFGHIJKLMNOP    1967-04-27 01:00:00

Upvotes: 1

Views: 3079

Answers (4)

Vincenzo Maggio
Vincenzo Maggio

Reputation: 3869

You need a list of friends ids:

SELECT U FROM DB.USERS U WHERE U.ID IN ( SELECT F.USERID2 FROM DB.FRIENDS F WHERE F.USERID1 = 16)

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

You should filter on the friends table, not the users table.

SELECT friends.*
FROM friends
INNER JOIN users
  ON friends.userid2 = users.id
WHERE friends.userid1 = 16

If you just need the friend ID's then there is not reason to join at all

SELECT userid2
FROM friends
WHERE userid1 = 16

Upvotes: 1

CMR
CMR

Reputation: 317

You could do a sub query like:

SELECT * 
FROM users
WHERE id IN
(
  SELECT userid2 as id FROM db.friends WHERE userid1 = 16
)

Upvotes: 2

What have you tried
What have you tried

Reputation: 11138

Add the condition for the user.id to your where clause at the end:

Select * From users
INNER JOIN friends on
users.id = friends.userid1
Where users.id = 16

Also, I would use an Inner Join which will return all records from users only where there is a match in friends

Upvotes: 1

Related Questions