Help with Subqueries using MYSQL here is my assignment. Create a table vHW1_8 sh

Question

Help with Subqueries using MYSQL

here is my assignment.

Create a table vHW1_8 showing the most commonly booked room type for each hotel in London. You need to show hotelname, NOT hotelno.

here is my code:

SELECT hotelname ,hotelno, type, MAX(y) as MostlyBooked FROM (SELECT hotelno, type, COUNT(type) AS y FROM booking b, hotel h, room r WHERE r.roomno = b.roomno AND r.hotelno = b.hotelno AND b.hotelno = h.hotelno AND city = 'London' GROUP BY hotelno, type) GROUP BY hotelno, type;

output:

ERROR 1248 (42000): Every derived table must have its own alias

Additional table Info:

Hotel = hotelno , hotelname, city

Room = roomno, hotelno, type, price

Booking = hotelno, guestno, datefrom, dateto, roomno

Explanation / Answer

The error is bcoz in subquery each table having alias name and some column present is 2 table(i.e. hotelno) .You have to specified which table used to retrive particular column.

SELECT drv.hotelname ,drv.hotelno, drv.type, y as MostlyBooked
FROM (SELECT h.hotelname,h.hotelno, r.type, COUNT(r.type) AS y
      FROM booking b, hotel h, room r
      WHERE r.roomno = b.roomno
     AND r.hotelno = b.hotelno
  AND b.hotelno = h.hotelno
  AND h.city = 'London'
  GROUP BY h.hotelname,h.hotelno, r.type
  having max(count(r.type)) = count(r.type)) drv;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.