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 trialHarris Handoko
3,932 PointsPlease critique my code for the North South Book Loan practice problem
Hello, I would appreciate if someone could tell me if this code could be made cleaner? It works, but I felt that it could be less bulky, I just don't know how.
-- Generate a report that lists a patron's first name, email and loan count for loans that haven't been returned.
SELECT p.first_name, p.email, SUM(outstd_loan) AS loan_count FROM patrons AS p
INNER JOIN (
SELECT ln.patron_id, count(ln.patron_id) AS outstd_loan FROM loans_north AS ln
WHERE ln.returned_on IS NULL
GROUP BY ln.patron_id
UNION ALL
SELECT ls.patron_id, count(ls.patron_id) FROM loans_south AS ls
WHERE ls.returned_on IS NULL
GROUP BY ls.patron_id
ORDER BY patron_id
) AS loanTotal ON p.id = loanTotal.patron_id
GROUP BY id;
Thanks!
4 Answers
Paul Boersma
4,542 Points-- Generate a report that lists a patron's first name, email and loan count for loans that haven't been returned.
SELECT first_name, email, COUNT(*) AS "# of non-returned books" FROM (
SELECT id, book_id, patron_id, returned_on FROM loans_north
WHERE returned_on IS NULL
UNION ALL
SELECT id, book_id, patron_id, returned_on FROM loans_south
WHERE returned_on IS NULL
) AS subQuery
INNER JOIN patrons ON patron_id = patrons.id
GROUP BY patrons.id
This solution seems to work for me and is somewhat cleaner I think.
Brian Taylor
3,436 PointsMine is about the same, after lots of playing with this I think you got it about as simple as can get. I'm just a student though 😅
SELECT first_name, email, id, SUM(count) AS outstanding_loans FROM patrons
INNER JOIN (
SELECT patron_id, COUNT(return_by) AS count FROM loans_north WHERE returned_on IS NULL GROUP BY patron_id
UNION ALL
SELECT patron_id, COUNT(return_by) AS count FROM loans_south WHERE returned_on IS NULL GROUP BY patron_id)
AS loan_counts
ON patrons.id = loan_counts.patron_id
GROUP BY id;
Nicole Buckenwolf
8,721 PointsI know this is older but replying anyway because going through this helped me clean up my own code. You don't need to reference all those columns from the loans tables, you just need the patron id associated with the loan.
SELECT first_name, last_name, email, COUNT(*) AS "Unreturned books" FROM (
SELECT patron_id FROM loans_north
WHERE returned_on IS NULL
UNION ALL
SELECT patron_id FROM loans_south
WHERE returned_on IS NULL
) AS loans
INNER JOIN patrons ON patron_id = patrons.id
GROUP BY patrons.id
ivana kantnerova
15,932 Pointscould be like this ... select patrons.first_name, patrons.email, count(*) as not_returned from patrons inner join loans_north on patrons.id = loans_north.patron_id inner join loans_south on patrons.id = loans_south.patron_id where loans_north.returned is NULL or loans_south.returned is NULL group by patrons.first_name,patrons.email;