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
Yi Ho Wong
6,377 PointsReview 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
Jason Anello
Courses Plus Student 94,610 PointsHi 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
9,110 PointsJason,
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;
Jason Anello
Courses Plus Student 94,610 PointsHi 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.
Andrew Phythian
19,747 PointsHere'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
27,996 PointsI 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;