Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases Querying Relational Databases Subqueries Review and Practice

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

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
15,083 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;