CODEWITHSUNDEEP
mysqlsqlnode.js
Leo
Leo

Reputation: 23

How to call a mysql in clause with a value match as well?

I have this query going and it return an empty array when in fact the two users are in the same Group_Members table. This is the query I put together to find mutual groups between two people.

"SELECT g.title, g.id FROM Groups g LEFT JOIN Group_Members g_m ON g.id = g_m.group_id WHERE g_m.user_id = ? AND g_m.group_id IN (?) ORDER BY g.id";

The array for the in is [1, 6, 8] and id 8 is the one that is expected to be returned.

Anyone able to let me know if I am messing something up.

Upvotes: 0

Views: 30

Answers (1)

danblack
danblack

Reputation: 14666

Use the Group_members twice, one for each user, and then the join is the overlap between them:

SELECT g.title,
       g.id
FROM Groups g1
JOIN Group_Members g_m1 ON g.id = g_m1.group_id
JOIN Group_members g_m2 ON g_m2.group_id = g_m1.group_id
WHERE g_m1.user_id = ?
  AND g_m2.user_id = ?
ORDER BY g.id

Upvotes: 2

Related Questions