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

Question

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

Search for *Relational Division*. `= ALL` only returns TRUE if all BUS_ID share the same value... — dnoeth, Jul 04 '19 at 10:07
@Jalal your question is: *the drivers who drive all the available buses*. So the table buses contains all the buses or all the available buses? — forpas, Jul 04 '19 at 10:34
@forpas Let me clarify, all the available buses mean all the records in bus table. — Jalal, Jul 04 '19 at 10:42

score 1 · Answer 1 · answered Jul 04 '19 at 10:08

1

Join the tables, group by d.driver_name and put the condition in the having clause:

select 
  d.driver_name
from bus b inner join driver d
on d.bus_id = b.bus_id
group by d.driver_name
having count(*) = (select count(*) from bus)

answered Jul 04 '19 at 10:08

forpas

160,666
10
38
76

Barbaros Özhan · Accepted Answer · 2019-07-04T10:16:44.420

1

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

edited Jul 04 '19 at 10:16

answered Jul 04 '19 at 10:09

Barbaros Özhan

59,113
10
31
55

Popeye · Answer 3 · 2019-07-04T14:53:13.860

0

Try this:

SELECT DISTINCT DRIVER_NAME FROM
(SELECT D.DRIVER_NAME, 
COUNT(DISTINCT BUS_ID) OVER (PARTITION BY D.DRIVER_NAME) INDVDL,
COUNT(DISTINCT BUS_ID) OVER () TOTAL
  FROM DRIVER D)
 WHERE INDVDL = TOTAL

-- update --

SELECT DISTINCT
    DRIVER_NAME
FROM
    (
        SELECT
            D.DRIVER_NAME,
            COUNT(DISTINCT BUS_ID) OVER(
                PARTITION BY D.DRIVER_NAME
            ) INDVDL,
            B.TOTAL   AS TOTAL
        FROM
            DRIVER D JOIN
            (
                SELECT
                    COUNT(1) AS TOTAL
                FROM
                    BUS
            ) B ON (1=1)
    )
WHERE
    INDVDL = TOTAL;

Cheers!!

edited Jul 04 '19 at 14:53

answered Jul 04 '19 at 10:16

Popeye

35,427
4
10
31

This will not work, if the there is a bus id 5 and none of the drivers drive it., then my answer should be null., but your query will give me max and ron. – Jalal Jul 04 '19 at 10:27
I thought you wanted only operated buses from Driver table. I have updated the answer now, Please check. – Popeye Jul 04 '19 at 14:53

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

3 Answers3