Sharina JonesPro 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;
Mustafa Başaran28,018 Points
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.