Databases Querying Relational Databases Subqueries Review and Practice

Harris Handoko
Harris Handoko
3,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;

Thanks!

2 Answers

Paul Boersma
Paul Boersma
4,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 kantnerova
ivana kantnerova
12,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;