CODEWITHSUNDEEP
sqljoinnatural-join
Maestro1
Maestro1

Reputation: 55

SQL Query Issue - Natural Join and table naming

I'm running into some difficulty with a query for my databases class. Given the following schema:

I'd like to find the customer(s) that have rented the most expensive room. I gave this query a try...

SELECT customerid FROM bookings NATURAL JOIN rooms 
EXCEPT
(SELECT customerid FROM (bookings NATURAL JOIN rooms) AS S, (bookings NATURAL JOIN
    rooms) as T WHERE S.price < T.price)

The problem comes from the way I want to rename the tables. I'd like to use the natural join of bookings and rooms as components of the Cartesian product... How can I do this?

Thank you very much.

Upvotes: 1

Views: 1645

Answers (2)

vaso123
vaso123

Reputation: 12391

SELECT * FROM customers as c
INNER JOIN bookings as b ON b.customerid = c.customerid
INNER JOIN rooms as r ON r.roomno = b.roomno
ORDER BY r.price DESC
LIMIT 1;

if you want only the names, or specified fields, you can use GROUP BY.

This query do the same (if the previous is good syntactically):

SELECT * FROM customers,bookings,rooms
WHERE bookings.customerid = customers.customerid
AND rooms.roomno = bookings.roomno
ORDER BY rooms.price DESC
LIMIT 1

so if you want id-s and names ordered by rent price desc:

SELECT customers.customerid, customers.fistname, customers.lastname FROM
customers,bookings,rooms
WHERE bookings.customerid = customers.customerid
AND rooms.roomno = bookings.roomno
ORDER BY rooms.price DESC
GROUP BY customers.customerid, customers.fistname, customers.lastname 
LIMIT 10

Upvotes: -1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

You could use this:

SELECT 
    customerid 
FROM 
    Bookings 
  NATURAL JOIN 
    Rooms
  NATURAL JOIN
    ( SELECT MAX(price) AS price
      FROM Rooms
    ) AS MostExpensiveRoom

Your query seems valid, except that you need to clarify which customerid you want in the second subquery, the S. or the T. one. The comma , syntax means a CROSS JOIN between S and T so you have two customerids:

(SELECT customerid FROM bookings NATURAL JOIN rooms)
EXCEPT
(SELECT S.customerid 
 FROM 
     (bookings NATURAL JOIN rooms) AS S
   CROSS JOIN
     (bookings NATURAL JOIN rooms) AS T 
 WHERE S.price < T.price
)

Upvotes: 2

Related Questions