CODEWITHSUNDEEP
sqldatabase
Error 404
Error 404

Reputation: 429

Find all users having rented every car in SQL

Find all users having rented every car in SQL

The database has the following structure (the primary keys are in bold):

My attempt: 

SELECT FROM rent 
WHERE FORALL cars EXISTS Daterent

Can you help me expressing correctly such a query?

Upvotes: 2

Views: 1496

Answers (1)

Stidgeon
Stidgeon

Reputation: 2723

From what you've provided, the following works.

First, you want to determine how many different/unique cars there are:

SELECT COUNT(DISTINCT car) FROM rent)

Next, you want to SELECT those people who have rented every different/unique car - so one way to do this is by checking that the COUNT of DISTINCT cars for each person is the same as the COUNT of all DISTINCT cars:

SELECT people
FROM rent 
GROUP BY people
HAVING COUNT(DISTINCT car) = (SELECT COUNT(DISTINCT car) FROM rent)

Upvotes: 2

Related Questions