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!
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
Harris Handoko3,932 Points
Please 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;
Paul Boersma4,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.
Mine 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;
ivana kantnerova15,932 Points
could 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;