Search code examples
sqloracle-databaserelational-division

How to fetch records from a table that matches with all values in the other table

How to write a query that fetches all the drivers who drive all the available buses?

TABLE BUS

--------
BUS_ID
--------
1
2
3
4

TABLE DRIVER

-----------------------
BUS_ID  |   DRIVER_NAME
-----------------------
1   |   John
2   |   John
1   |   Max
2   |   Max
3   |   Max
4   |   Max
2   |   Bill
3   |   Ron
4   |   Ron
1   |   Ron
2   |   Ron

In this example it should return 

DRIVER_NAME
-----------
Max
Ron

Note: Do not hard code any values in the query.

I have written the following

SELECT DRIVER.DRIVER_NAME
FROM   DRIVER
WHERE  BUS_ID = ALL( SELECT BUS_ID FROM BUS);
Solution

  • Use grouping with having clause :

    SELECT D.DRIVER_NAME
  FROM DRIVER D
 GROUP BY D.DRIVER_NAME
HAVING COUNT(*) = (SELECT COUNT(*) FROM BUS);

DRIVER_NAME
-----------
Ron
Max

    Demo