Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Querying Relational Databases Subqueries Review and Practice

Sharina Jones
PLUS
Sharina Jones
Courses Plus Student 18,771 Points
Posted by Sharina Jones
Sharina Jones
Courses Plus 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,046 Points
Mustafa Başaran
Mustafa Başaran
28,046 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.

Jorgen Rasmussen
Jorgen Rasmussen
3,367 Points
Jorgen Rasmussen
Jorgen Rasmussen
3,367 Points

Thanks Mustafa! I really struggled with this one and your answer is the most concise and makes good sense.