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 trialjlampstack
23,932 PointsIs 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
11,747 PointsHi 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
Balazs Peak
46,160 PointsYour solution seems perfect, good job!
jlampstack
23,932 Pointsjlampstack
23,932 PointsThanks Mark, appreciate your advice in simplifying it even more.