I am new to SQL and need assistance to combine (JOIN) two subqueries with a main query in SQL. Here is what I have. *Each query works independent of another. The end result would be that I would retrieve the # of accommodations for each resort, retrieve the lowest cost of all accommodations for each resort, and join those results to the list of resort types and resorts.
Table 1 - Resort - resort_id (PK) resort_type_id (FK) name Table 2 - Resort_type - resort_type_id (PK) resort_type Table 3 - Accommodations - accommodations_id (PK) resort_id (FK) description cost_per_night
SELECT Resort.name, Resort_type, Acc.Accommodations, Low.min_price FROM (SELECT resort.name AS resort_name, Resort_type.resort_type FROM Resort INNER JOIN Resort_type ON Resort.resort_type_id = Resort_type.resort_type_id (SELECT resort_id, Count(resort_id) AS Accommodations FROM Accommodations GROUP BY resort_id) AS Acc (SELECT resort_id, Min(cost_per_night) AS min_price FROM Accommodations GROUP BY resort_id) AS Low
Any guidance would be greatly appreciated. I am having a difficult time visualizing how this should come together.