get multiple rows from table where column = array

Question

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.

Juan Carlos Oropeza · Accepted Answer · 2017-11-22 13:05:46Z

1

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`)

answered Nov 22, 2017 at 13:05

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chud37 Over a year ago

Can you explain what is going on here? Specifically the COUNT(CASE bit? It does seem to work though!

Juan Carlos Oropeza Over a year ago

Is a conditional COUNT(), basically count how many user are in that roomId group with that characteristic. you can add THEN 1 ELSE NULL but for CASE expression that is the deffault and COUNT() doesnt count NULLs Finally you check if the total_match is equal to the total of user on that room to make sure everyone and only them are there.

Chud37 Over a year ago

Thanks, it works! I wouldnt have been able to figure that out for myself :)

Collectives™ on Stack Overflow

get multiple rows from table where column = array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related