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 Joining Table Data with SQL Review and Practice

Ethan Martin
PLUS
Ethan Martin
Courses Plus Student 1,883 Points
Posted by Ethan Martin
Ethan Martin
Courses Plus Student 1,883 Points

Identifying the Patrons with outstanding Book Loans and the Count of their Outstanding Books

SELECT patrons.first_name, COUNT(loans.patron_id) AS "Books Outstanding", patrons.email FROM patrons INNER JOIN loans ON patrons.id = loans.patron_id GROUP BY patrons.first_name HAVING returned_on IS NULL;

first_name - Books Outstanding - email

Alena 37 alena.holligan@teamtreehouse.com

Andrew 19 andrew.chalkley@teamtreehouse.com

Dave 18 dave.mcfarland@teamtreehouse.com

This is what gets returned but I know this is wrong

Alena should =4 Dave should = 4 Andrew should = 4

What am I doing wrong. I want to select the NAME, COUNT, and their EMAIL. It is important to count only the IDs where the RETURN DATE is empty.

5 Answers

KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,967 Points
KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,967 Points

You should use a WHERE clause instead of HAVING

SELECT patrons.first_name, COUNT(loans.patron_id) AS "Books Outstanding", patrons.email FROM loans INNER JOIN patrons ON patrons.id = loans.patron_id WHERE loans.returned_on IS NULL GROUP BY loans.patron_id
Ikechukwu Arum
Ikechukwu Arum
5,337 Points
Ikechukwu Arum
Ikechukwu Arum
5,337 Points 
SELECT patrons.first_name AS "First_Name", patrons.email AS "Emails", COUNT(loans.patron_id) AS "COUNT" FROM loans INNER JOIN patrons ON patrons.id = loans.patron_id GROUP BY loans.patron_id,returned_on HAVING loans.returned_on IS NULL;
Ingenious Courses
Ingenious Courses
5,353 Points
Ingenious Courses
Ingenious Courses
5,353 Points

Hi @ikechukwumerije! Why did you use FROM loans first instead of FROM patrons?

Ikechukwu Arum
Ikechukwu Arum
5,337 Points
Ikechukwu Arum
Ikechukwu Arum
5,337 Points

I think you tagged the wrong person but the question is asking for outstanding book loans so I would imagine you'd look at the loans table to get the information

R S
R S
2,255 Points
R S
R S
2,255 Points

Having and where are both filtering keywords. The key to when to use which keyword for filtering is WHEN you want to filter. If you want to filter the records BEFORE you group them, use the WHERE keyword. If you want to filter the result set AFTER you group the records then use the HAVING keyword In this case you want to filter out those loans that were already returned BEFORE you group those patrons with outstanding loans...Why would you want to group patrons that don't even have any outstanding loan?!