CODEWITHSUNDEEP
mysql
user2709610
user2709610

Reputation:

Intersect with MySQL

SELECT friend_id FROM friendships WHERE user_id = 1;

Returns:
+-----------+
| friend_id |
+-----------+
|         2 |
|         3 |
+-----------+

SELECT friend_id FROM friendships WHERE user_id = 2;

Returns:
+-----------+
| friend_id |
+-----------+
|         1 |
|         3 |
|         6 |
+-----------+

I'm trying to find an alternative to INTERSECT for MySQL. I need to get the common row(s) between these two queries.

Upvotes: 0

Views: 40

Answers (3)

Calvin LIu
Calvin LIu

Reputation: 1

Have you tried this query?

Since we are trying to find the mutual friends between user 1 and user 2, by selecting friend_id from friendships and (user_id=1 or user_id =2) gives us the list of friends both users have. When group by friend_id and having count(friend_id)>1 are used, we are querying the friends that they have in common.

SELECT friend_id FROM friendships
WHERE user_id = 1 or user_id=2
GROUP BY friend_id
Having count(friend_id)>1

Upvotes: 0

Simon R
Simon R

Reputation: 3772

You can use a join to replace the INTERSECT

SELECT DISTINCT f1.friend_id 
FROM friendships f1
JOIN friendships f2 ON f1.friend_id = f2.friend_id
WHERE f1.user_id = 1 and f2.user_id = 2;

Upvotes: 1

P.Shah
P.Shah

Reputation: 1

Try This:

Select A.friend_id from

(SELECT friend_id FROM friendships WHERE user_id = 1 ) A

join

(SELECT friend_id FROM friendships WHERE user_id = 2) B on A.friend_id = B.friend_id

Upvotes: 0

Related Questions