Databases Querying Relational Databases Subqueries Review and Practice

Sharina Jones
PRO
Sharina Jones
Pro Student 18,771 Points

Combining the results of subqueries

I believe the question is asking us to combine the sums of the two subqueries. Unfortunately, I'm not sure how to do that.

I've managed to get each total individually. Can someone help me with how to add the totals together?

-- Generate a report that lists a patron's first name, email and loan count for loans that haven't been returned.
SELECT pt.first_name, pt.email,  Loc1.northAmount, Loc2.southAmount FROM patrons AS pt
  LEFT OUTER JOIN(
    SELECT patron_id, count(loaned_on) AS northAmount
    FROM loans_north AS n WHERE n.returned_on IS NULL
    GROUP BY patron_id
  ) AS Loc1 ON pt.id = Loc1.patron_id
  LEFT OUTER JOIN(
     SELECT patron_id, count(loaned_on) AS southAmount
    FROM loans_south AS s WHERE s.returned_on IS NULL
    GROUP BY patron_id
  ) AS Loc2 ON pt.id = Loc2.patron_id;

Thanks.

1 Answer

Mustafa Başaran
Mustafa Başaran
28,018 Points

Hi Sharina,

You may want to try using a UNION ALL to get patron_ids belonging to unreturned loans from both south and north combined. Then, use this as subquery join on patrons table. The column to join on is of course the id columns. Then you can group by id or name as they are both unique.

SELECT first_name, email, COUNT(*) AS total_loans FROM patrons
INNER JOIN 
(SELECT patron_id FROM loans_south WHERE returned_on IS NULL
UNION ALL
SELECT patron_id FROM loans_north WHERE returned_on IS NULL) AS loans
ON patrons.id = loans.patron_id
GROUP BY first_name;

I hope this works.