Databases Querying Relational Databases Joining Table Data with SQL Review and Practice

Ethan Martin
PRO
Ethan Martin
Pro 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.

4 Answers

KRIS NIKOLAISEN
KRIS NIKOLAISEN
53,388 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
3,495 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

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

Ikechukwu Arum
Ikechukwu Arum
3,495 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