[SOLVED] select values which coincide with all the elements of the list

Issue

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?

Solution

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
;

Answered By – Abhik Chakraborty

Answer Checked By – Cary Denson (BugsFixing Admin)