'select values which coincide with all the elements of the list

How to make mysql requst to select all values, that contains all elements of list. I have table like this:

+--------+------------+
| carID  | serviceID  |
+--------+------------+
|   1    |      12    |
+--------+------------+
|   1    |      13    |
+--------+------------+
|   1    |      15    |
+--------+------------+
|   2    |      12    |
+--------+------------+
|   3    |      13    |
+--------+------------+
|   3    |      15    |
+--------+------------+

I make request like:

SELECT `carID` FROM `car_services` WHERE `carID` in (1, 2, 3) AND `serviceID` in (12, 13, 15);

but it returns all cars, but I need just car with carID = 1, because only this car suitable for the condition (support all services 12, 13, 15).

Any ideas?

mysql


Solution 1:[1]

One easy way would be group by and having

SELECT `carID` 
FROM `car_services` 
WHERE `carID` in (1, 2, 3) AND `serviceID` in (12, 13, 15)
group by carID
having count(*) = 3
;

Solution 2:[2]

Use GROUP BY and HAVING clauses:

SELECT `carID` 
FROM `car_services` 
WHERE `carID` in (1, 2, 3) 
GROUP BY `carID`
HAVING SUM(CASE WHEN `serviceID` in (12,13,15) THEN 1 ELSE 0 END)=3

Solution 3:[3]

Using a compound where clause to individually check EXISTS is pretty straightforward and not dependent on the uniqueness of the serviceid-carid relationship. I look at the query plan/execution cost before deciding this was actually better than the the grouping solutions if it unique.

SELECT carID
  FROM car_services a
 WHERE EXISTS (SELECT 1 FROM car_services b WHERE a.carID = b.car_id AND serviceID = 12)
   AND EXISTS (SELECT 1 FROM car_services b WHERE a.carID = b.car_id AND serviceID = 13)
   AND EXISTS (SELECT 1 FROM car_services b WHERE a.carID = b.car_id AND serviceID = 15)

NOTE: This clearly doesn't scale well for lots of required services.

Solution 4:[4]

You can use GROUP_CONCAT:

SELECT carID
FROM (
  SELECT carID, 
         GROUP_CONCAT(DISTINCT serviceID ORDER BY serviceID) AS allServices
  FROM car_services
  WHERE serviceID in (12, 13, 15)
  GROUP BY carID ) t
WHERE allServices = "12,13,15";

This will work even if you have duplicates in serviceID field.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Abhik Chakraborty
Solution 2 Raging Bull
Solution 3 Karl Kieninger
Solution 4

Related Questions