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 trial

Databases Querying Relational Databases Subqueries Review and Practice

Harris Handoko
Harris Handoko
3,932 Points
Posted by 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!

4 Answers

Paul Boersma
Paul Boersma
4,542 Points
Paul Boersma
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
Brian Taylor
3,436 Points
Brian Taylor
Brian Taylor
3,436 Points

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;
Nicole Buckenwolf
Nicole Buckenwolf
8,720 Points
Nicole Buckenwolf
Nicole Buckenwolf
8,720 Points

I 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
ivana kantnerova
15,932 Points
ivana kantnerova
ivana kantnerova
15,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;