Combine two virtual tables from separate queries

Question

I've got two SQL queries. One returns all the payments made to a company for a swimming event The other gets all the seating payments (separate from the first).

The first one has a virtual table structure of carnivalId, buildingCode, TotalRaces, TotalIncome

The second is carnivalId, SeatCosts

I want to combine them into one table so the query returns in the format of carnivalId, buildingCode, TotalRaces, TotalIncome, SeatCosts

I've looked into UNION, but it doesn't seem to be the correct approach as the two queries have different return structures.

I've tried joining the two with an inner join on carnivalId, but it just errors -

SELECT c.carnivalId, c.buildingCode, COUNT(r.raceId) as TotalRaces, SUM(p.amountPaid) as TotalIncome
FROM Carnival c, Race r, Payment p, Entry e
WHERE r.carnivalId = c.carnivalId
AND e.raceId = r.raceId
AND p.payId = e.payId
INNER JOIN (SELECT c.carnivalId, SUM(s.seatsBought*st.seatCost)
    FROM Carnival c, SeatsAvailable s, SeatType st
    WHERE carnivalId = c.carnivalId
    AND st.seatType = s.seatType
    GROUP BY c.carnivalId, c.buildingCode) SeatingCosts ON c.carnivalId = SeatingCosts.carnivalId
GROUP BY c.carnivalId, c.buildingCode;

I also tried combining the two queries into one, but it made all the returned values completely wrong (it looks like it multiplied all the values in the first by the number of rows in the seatsBought result

SELECT c.carnivalId, c.buildingCode, COUNT(r.raceId) as TotalRaces, SUM(p.amountPaid) as TotalIncome, SUM(s.seatsBought*st.seatCost) as SeatCosts
FROM Carnival c, Race r, Payment p, Entry e, SeatsAvailable s, SeatType st
WHERE r.carnivalId = c.carnivalId
AND e.raceId = r.raceId
AND p.payId = e.payId
AND s.carnivalId = c.carnivalId
AND st.seatType = s.seatType
GROUP BY c.carnivalId, c.buildingCode;

First query -

SELECT c.carnivalId, c.buildingCode, COUNT(r.raceId) as TotalRaces, SUM(p.amountPaid) as TotalIncome
FROM Carnival c, Race r, Payment p, Entry e
WHERE r.carnivalId = c.carnivalId
AND e.raceId = r.raceId
AND p.payId = e.payId
GROUP BY c.carnivalId, c.buildingCode;

Second query -

SELECT c.carnivalId, SUM(s.seatsBought*st.seatCost) as SeatCosts
FROM Carnival c, SeatsAvailable s, SeatType st
WHERE s.carnivalId = c.carnivalId
AND st.seatType = s.seatType
GROUP BY c.carnivalId, c.buildingCode;

score 1 · Accepted Answer · edited Jul 22 '20 at 20:40

You should join the result of aggregation eg using your query as subquery and not join the disaggregated rows

SELECT t1.carnivalId
      , t1.buildingCode
      , t1.TotalRaces
      , t1.TotalIncome
      , t2.SeatCosts 
  FROM (

    SELECT c.carnivalId
      , c.buildingCode
      , COUNT(r.raceId) as TotalRaces
      , SUM(p.amountPaid) as TotalIncome
    FROM Carnival c
    INNER JOIN Race r ON r.carnivalId = c.carnivalId
    INNER JOIN Entry e ON e.raceId = r.raceId
    INNER JOIN Payment p ON p.payId = e.payId
    GROUP BY c.carnivalId, c.buildingCode
 ) t1 
INNER JOIN (
  SELECT c.carnivalId
    , SUM(s.seatsBought*st.seatCost) as SeatCosts
  FROM Carnival c
  INNER JOIN SeatsAvailable s ON s.carnivalId = c.carnivalId
  INNER JOIN  SeatType st ON st.seatType = s.seatType
  WHERE s.carnivalId = c.carnivalId
  GROUP BY c.carnivalId, c.buildingCode
) t2 ON t1.carnivalId = t2.carnivalId

And you should use the explicit join syntax based on JOIN explicit clause and avoid old (pre-1992) implicit join syntax based on where clause.

Is there any reason for the lowercase select and from at the start? Does it have any impact — , Sep 08 '19 at 13:58
Problem is with MS access with multiple inner joins. Need parentheses as described here -- https://stackoverflow.com/questions/20929332/multiple-inner-join-sql-access — , Sep 08 '19 at 14:21

Combine two virtual tables from separate queries

1 Answers1