How to select particular column from table if another column includes all the set of requested values?

Question

I have a table in MySQL named table1 which has three columns id, user_id(foreign_key) and destination_id(foreign_key). One user can have multiple destinations.

E.g Table1

id      user_id       destination_id
1         10            2
2          5            3
3         10            4
4         10            5
5          9            10
6          5            12
7          8            2

I get a request from the client side in PHP script; the request includes destination ids in an array.

E.g. $request = array('destination_id' => [2,4,5]);

I just want to get all the user_id from table1 if and only if the particular user_id contains all requested destinations.

I tried to achieve this using 'IN' operator. i.e.

     SELECT user_id FROM table1 WHERE destination_id IN ($requestedDestinationsInCommaSeparatedString)

It gives row including user_id 8 along with user_id 10 but I just need user_id 10. I just wanted to know the concept regarding the solution to the following problem. I am a beginner in SQL, any help would be very appreciable. Thanks.

You had better show us how you created `$requestedDestinationsInCommaSeparatedString` from that array — RiggsFolly, Aug 08 '18 at 14:14
In your case, you cannot use IN, because IN will tell the query to look for any user_id that fit on ANY of the destination_id you provided. — Rafael, Aug 08 '18 at 14:17
Can I check that you really mean this statement _if the particular user_id contains_ **all** _requested destinations_ — RiggsFolly, Aug 08 '18 at 14:19
See this, Niraj. Seems exactly like your problem: https://stackoverflow.com/questions/15977126/select-rows-that-match-all-items-in-a-list — Rafael, Aug 08 '18 at 14:28
@RiggsFolly: $requestedDestinationsInCommaSeparatedString just means the conversion result of requested destination array into string so that it fit inside IN operator in mysql. For example: In above scenario, it is $requestedDestinationsInCommaSeparatedString = "2,4,5". — nirazlatu, Aug 08 '18 at 14:45

score 6 · Accepted Answer · answered Aug 08 '18 at 14:38

6

You can check that a user_id refers to all requested destination by grouping and counting the destinations.

SELECT user_id
FROM table1 
WHERE
    destination_id IN (2,4,5)
GROUP BY
    user_id
HAVING count(*) = 3
-- count must be the number of elments in (2,4,5)

For doing so, the field combination of user_id and destination_id must be unique over all records.

answered Aug 08 '18 at 14:38

Henrik

2,771
1
23
33

Thank you I will try this approach. – nirazlatu Aug 08 '18 at 14:50
Thanks @Henrik :) – nirazlatu Aug 08 '18 at 15:03

score 0 · Answer 2 · answered Aug 08 '18 at 14:35

The only thing I can think of is to use multiple subselects and build the query string in PHP.

So for your specific example the SQL-Query-String generated should be

SELECT user_id
FROM table1 

WHERE user_id IN
    (SELECT user_id FROM table1 WHERE destination_id = 2)
AND user_id IN
    (SELECT user_id FROM table1 WHERE destination_id = 4)
AND user_id IN
    (SELECT user_id FROM table1 WHERE destination_id = 5)

GROUP BY user_id

I think programming the function which generates the middle part for you shouldn't be too hard.

How to select particular column from table if another column includes all the set of requested values?

2 Answers2