CODEWITHSUNDEEP
mysql
Chud37
Chud37

Reputation: 5017

get multiple rows from table where column = array

This is a revised question of my previous question, because I dont think I asked it correctly.

I am creating a chat room in NodeJS, and in order to do that I have the following table that lists which users are in each room:

+----+--------+--------+--+
| id | roomID | userID |  |
+----+--------+--------+--+
|  1 |      9 |      3 |  |
|  2 |      9 |   4786 |  |
|  3 |      9 |   7991 |  |
|  4 |      7 |      3 |  |
|  5 |      7 |   4786 |  |
|  6 |      1 |      3 |  |
+----+--------+--------+--+

I need to be able to select from the list the roomID from an array of users. So lets say, I want to get the roomID that users 4786 and 3 are using, It would return 7. If I wanted to get the roomID that users 4786,3 AND 7991 are using, it would return 9.

Okay so. In my previous question the answer I got was to use SELECT * FROM table WHERE userID IN (4786,3,7991). What I didnt realise (I dont use the IN statement that much) was that the IN statement was essentially a shortcut for the OR statement.

Which leads me to the problem, The first thing I tried was OR and because it doesnt explicitly match each userID in the table, the results are unreliable.

I've created an SQLFiddle here.

I want to be able to say: Give me the unique roomID for these users and and these users only. It doesnt matter if they are involved in other rooms.

I hope i've made myself a bit more understandable.

Upvotes: 0

Views: 22

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SQL DEMO

SELECT roomId, COUNT(CASE WHEN `userID` IN (4786,3) 
                          THEN 1
                     END) total_match
FROM `chat-room-users` 
GROUP BY roomId
HAVING total_match = 2  -- because are 2 users in (4786,3) 
   AND total_match = COUNT(`userID`)

Lets say you put your users in a table search_user to be more generic your query become:

SELECT roomId, COUNT(CASE WHEN `userID` IN (SELECT userID FROM search_users) 
                          THEN 1
                     END) total_match
FROM `chat-room-users` 
GROUP BY roomId
HAVING total_match = (SELECT COUNT(*) FROM search_users) 
   AND total_match = COUNT(`userID`)

Upvotes: 1

Related Questions