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

Posted by jlampstack
jlampstack
23,932 Points

Is my solution correct?

I've had trouble figuring this out and have seen many solutions, which all seem to vary. I am wondering if my solution is correct? Or if it can be improved on? Any feedback appreciated. Thanks!!

-- Generate a report that lists a patron's first name, email and loan count for loans that haven't been returned.

SELECT 
  first_name AS "First Name", 
  email AS "E-mail",
  ln.tally AS "North", 
  ls.tally AS "South",
  ls.tally + ln.tally AS "Total Outstanding Books"
FROM patrons AS p
LEFT OUTER JOIN(
  SELECT COUNT(*) AS tally, ln.patron_id FROM loans_north AS ln
  INNER JOIN books_north AS bn ON bn.id = ln.book_id
  WHERE ln.returned_on IS NULL
  GROUP BY ln.patron_id
) AS ln ON p.id = ln.patron_id
LEFT OUTER JOIN(
  SELECT COUNT(*) AS tally, ls.patron_id FROM loans_south AS ls
  INNER JOIN books_south AS bs ON bs.id = ls.book_id
  WHERE ls.returned_on IS NULL
  GROUP BY ls.patron_id
) AS ls ON p.id = ls.patron_id;

2 Answers

Mark Chesney
Mark Chesney
11,747 Points

Hi jaycode. Yes, it is correct -- in fact it's very well helping me! Hat tip to you.

One pointer I can offer: the JOIN on books_north and books_south don't appear necessary for the purpose of this question. I accomplish in my table:

SELECT COUNT(*), patron_id FROM
loans_north
WHERE returned_on IS NULL
GROUP BY patron_id

...what you do in yours:

SELECT COUNT(*) AS tally, ln.patron_id FROM loans_north AS ln
  INNER JOIN books_north AS bn ON bn.id = ln.book_id
  WHERE ln.returned_on IS NULL
  GROUP BY ln.patron_id
jlampstack
jlampstack
23,932 Points

Thanks Mark, appreciate your advice in simplifying it even more.

Balazs Peak
Balazs Peak
46,160 Points
Balazs Peak
Balazs Peak
46,160 Points

Your solution seems perfect, good job!