I am having a problem getting my query to work. The query needs to 1) return all

Question

I am having a problem getting my query to work.  The query needs to 1) return all resort types (rt.resort_type; and 2)  the names (r.name) of any resort types with less than 3 accommodations (a.accommodations).  My subquery after the IN in the WHERE clause works, but I'm not sure I have it in the right place or if the rest of the query is even right.

SELECT rt.resort_type_id, rt.resort_type, r.NAME, count(a.accommodations_id) AS Rooms
FROM resort_type rt
JOIN resort r ON rt.resort_type_id = r.resort_type_id
JOIN accommodations A ON A.resort_id = r.resort_id
WHERE A.accommodations_id IN (SELECT r.resort_id, count(A.accommodations_id) AS Rooms FROM accommodations
  WHERE (A.accommodations_id < 3) GROUP BY r.resort_id)
GROUP BY rt.resort_type_id, rt.resort_type, r.NAME;

I am late on this assignment and really need some help, please.  Thanks!

Explanation / Answer

which u give in count same should be in group by clause

SELECT rt.resort_type_id, rt.resort_type, r.NAME, count(a.accommodations_id) AS Rooms
FROM resort_type rt
JOIN resort r ON rt.resort_type_id = r.resort_type_id
JOIN accommodations A ON A.resort_id = r.resort_id
WHERE A.accommodations_id IN (SELECT r.resort_id, count(A.accommodations_id) AS Rooms FROM accommodations
WHERE (A.accommodations_id < 3) GROUP BY A.accomodation)
GROUP BY rt.resort_type_id, rt.resort_type, r.NAME;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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