Harris Handoko3,393 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,530 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.
ivana kantnerova12,494 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;