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

Yi Ho Wong
Yi Ho Wong
6,377 Points

Review and Practice - User Loans

-- Generate a report that lists a patron's first name, email and loan count for loans that haven't been returned.

Below is my draft that have not use subquery:

SELECT first_name, email FROM patrons;

SELECT patron_id, COUNT(*) FROM loans_north 
  WHERE returned_on IS NULL
GROUP BY patron_id
UNION 
SELECT patron_id, COUNT(*) FROM loans_south 
  WHERE returned_on IS NULL 
GROUP BY patron_id
ORDER BY patron_id;

I get stuck on getting the the loan count for loans that haven't been returned. May I know how to get the result?

4 Answers

Hi Yi Ho Wong,

A UNION ALL on the 2 loans tables with WHERE clauses will get you all the book loans that haven't been returned yet.

SELECT patron_id FROM loans_south WHERE returned_on IS NULL
UNION ALL
SELECT patron_id FROM loans_north WHERE returned_on IS NULL

This can be your subquery. Then you can INNER JOIN the patrons table with this temporary table to get all the columns requested.

SELECT first_name, email, COUNT(*) AS "Loan Count" 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 patron_id
Henry Williams
Henry Williams
9,110 Points

Jason,

I used LEFT OUTER JOIN and the COUNT applied to the patron_id generated in the derived table. In this way, you also identify the fact that Craig has zero outstanding loans.

Do you agree that this may be what is required and if so, is a correct and robust method of achieving it?

SELECT patrons.first_name, patrons.email, COUNT(loans.patron_id) AS loan_count FROM patrons
  LEFT OUTER 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 loans.patron_id = patrons.id
  GROUP BY patron_id
  ORDER BY loan_count DESC;

Hi Henry,

I seem to remember one challenge where it was useful to use an outer join but I don't think it is necessary here.

The way I'm interpreting the question is that we're interested in patrons that have books that still need to be returned and we need to know how many they have.

Presumably, you would be running this query to maybe send out email reminders if their book is due soon or to add fines if it's past the date.

Craig doesn't have any books that need to be returned so I wouldn't think that he should be included in the report.

Here's my code

SELECT p.id, p.first_name, p.email, COUNT(l.patron_id) AS Unreturned FROM patrons AS p INNER JOIN (SELECT patron_id FROM loans_north WHERE returned_on IS NULL
 UNION ALL 
SELECT patron_id FROM loans_south WHERE returned_on IS NULL ORDER BY patron_id) AS l ON p.id = l.patron_id GROUP BY patron_id;
Ismail Qaznili
Ismail Qaznili
27,996 Points

I tried the following and got me a result similar to code in Jason Anello's answer.

SELECT patrons.first_name, patrons.email, COUNT(book_id) FROM (
SELECT * FROM (SELECT * FROM loans_north WHERE returned_on IS null)
UNION ALL
SELECT * FROM (SELECT * FROM loans_south WHERE returned_on IS null)
) LEFT OUTER JOIN patrons ON patrons.id = patron_id
GROUP BY patron_id;