CODEWITHSUNDEEP
sql-server
troyB
troyB

Reputation: 1

Combine two subqueries to main query in SQL

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.

DB Schema 

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

Query

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.

Upvotes: 0

Views: 907

Answers (2)

zedfoxus
zedfoxus

Reputation: 37029

The query below lists each resort and its type along with the number of accommodations and the lowest cost per night.

select
  r.name,
  t.resort_type as type,
  count(a.accommodations_id) as accommodations,
  min(cost_per_night) as lowestcost
from resort r
inner join resort_type t
  on t.resort_type_id = r.resort_type_id
left join accommodations a
  on a.resort_id = r.resort_id
group by r.name, t.resort_type

Example: http://sqlfiddle.com/#!9/fc089/6

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

Is this what you're looking for:

SELECT
    r.name,
    rt.resort_type,
    t.no_of_accommodations,
    t.min_price
FROM Resort r
INNER JOIN Resort_Type rt
    ON rt.resort_type_id = r.resort_type_id
LEFT JOIN(
    SELECT 
        COUNT(*) AS no_of_accommodations,
        MIN(cost_per_night) AS min_price
    FROM Accommodations
    GROUP BY resort_id
)t
    ON t.resort_id = r.resort_id

Upvotes: 0

Related Questions