CODEWITHSUNDEEP
mysqlsql
Gregory Siot
Gregory Siot

Reputation: 27

Finding all or none

I have four tables detailing an amusement park and its guests' ride history.

   Categories
  c_id    name
  1    Thrill
  2    Leisure
  3    Kiddie

    Rides 
 r_id  c_id 
  1     1
  2     1
  3     2
  4     2
  5     3
  6     3

guest_history
h_id   g_id
 1      1
 2      1
 3      2
 4      3

history_items
 h_id  r_id
  1     5
  2     6
  3     1
  3     2
  4     5

How would I get all of the guests (g_id's) that have either rode all of the kiddie rides or none of the kiddie rides?

Expected Output would be: 

g_id
 1
 2

I can't seem to figure out what the easiest way to go about it would be. I can only seem to conjure up a table that contains all of the cases that a guest has rode a kiddie ride. My attempt was a 4-way inner join of the tables and to filter out on the c_id = "Kiddie". Any help would be appreciated.

Upvotes: 0

Views: 56

Answers (2)

Akina
Akina

Reputation: 42622

SELECT g_id
FROM Categories
NATURAL JOIN Rides
NATURAL JOIN guest_history
NATURAL JOIN history_items
GROUP BY g_id
HAVING COUNT(DISTINCT r_id) IN (0,
                                (SELECT COUNT(DISTINCT r_id)
                                 FROM Categories
                                 NATURAL JOIN Rides
                                 WHERE name = 'Kiddie')
                               )

fiddle

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

You can try this below logic-

3 is fixed in the query to get category "Kiddie"

DEMO HERE

SELECT A.g_id,COUNT(C.r_id)
FROM guest_history A
INNER JOIN history_items B ON A.h_id = B.h_id
INNER JOIN Rides C ON B.r_id = C.r_id AND C.c_id = 3
GROUP BY A.g_id 
HAVING COUNT(DISTINCT B.r_id) = (SELECT COUNT(r_id) FROM Rides WHERE c_id = 3)
OR COUNT(DISTINCT B.r_id) = 0

Upvotes: 2

Related Questions