CODEWITHSUNDEEP
sql
esbej
esbej

Reputation: 27

SQL correct query or not

table relationships

given these relationships, how could you query the following: The tourists (name and email) that booked at least a pension whose rating is greater than 9, but didn't book any 3 star hotel with a rating less than 9.

Is the following correct?

SELECT Tourists.name, Tourists.email
FROM Tourists
WHERE EXISTS (
    SELECT id FROM Bookings
    INNER JOIN Tourists ON Bookings.touristId=Tourists.id 
    INNER JOIN AccomodationEstablishments ON Bookings.accEstId=AccomodationEstablishments.id
    INNER JOIN AccomodationTypes ON AccomodationEstablishments.accType=AccomodationTypes.id 
    WHERE   AccomodationTypes.name = 'Pension' AND 
        AccomodationEstablishments.rating > 9
) AND NOT EXISTS (
    SELECT id FROM Bookings
    INNER JOIN Tourists ON Bookings.touristId=Tourists.id 
    INNER JOIN AccomodationEstablishments ON Bookings.accEstId=AccomodationEstablishments.id
    INNER JOIN AccomodationTypes ON AccomodationEstablishments.accType=AccomodationTypes.id 
    WHERE   AccomodationTypes.name = 'Hotel' AND 
        AccomodationEstablishments.noOfStars = 3 AND 
        AccomodationEstablishments.rating < 9
)

Upvotes: 0

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would do this using aggregation and having:

SELECT t.name, t.email
FROM Bookings b INNER JOIN
     Tourists t
     ON b.touristId = t.id INNER JOIN
     AccomodationEstablishments ae
     ON b.accEstId = ae.id INNER JOIN
     AccomodationTypes a
     ON ae.accType = a.id 
GROUP BY t.name, t.email
HAVING SUM(CASE WHEN a.name = 'Pension' AND ae.rating > 9 THEN 1 ELSE 0 END) > 0 AND
       SUM(a.name = 'Hotel' AND ae.noOfStars = 3 AND ae.rating < 9 THEN 1 ELSE 0 END)= 0;

Your method also works, but you probably need t.id in the subqueries.

Upvotes: 1

Related Questions