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
Brane Opačič
6,686 PointsSELECT all patrons with "outstanding books"...
Hello, I happen to have a minor problem with this task. It's not as much as I don't know how to solve it, as much as I don't quite understand the question.
The full text goes like this:
"-- Use a JOIN to select all patrons with outstanding books. Select their first name and email address."
Is this the correct way of doing so:
SELECT first_name, last_name FROM patrons INNER JOIN loans ON patrons.id = loans.patron_id;
I return the patrons first name and last name.
Thank you!
7 Answers
Ryan Arthur
4,910 PointsI found without DISTINCT, you get duplicate results. Fun challenge!
SELECT DISTINCT first_name, email FROM patrons INNER JOIN loans ON patrons.id = loans.patron_id WHERE loans.returned_on IS NULL;
Jonathan Grieve
Treehouse Moderator 91,253 PointsHi there,
You need to return a more specific set of records in your query. You can do this with a WHERE clause that goes at the end of your query.
So you want to return loads that have no yet been returned. I'm not sure what the column for that is but if you try something like
SELECT first_name, last_name FROM patrons INNER JOIN loans ON patrons.id = loans.patron_id WHERE returned_on = null;
You should be on the right lines.
harishupadhyayula
32,221 PointsThis query will not work because, the "return_by" date on those "null" columns are in future, which means, they still have time to return the book and are not overdue.
SELECT p.first_name, p.last_name,l.return_by, l.returned_on
FROM loans as l
INNER JOIN patrons as p
ON p.id = l.patron_id
WHERE return_by < DATE("now")
AND returned_on = null
Marcia Haledjian
7,562 PointsThe only error on your statement is: returned_on = null, the correct is: returned_on IS null
Marcia Haledjian
7,562 PointsSELECT patrons.first_name, patrons.last_name FROM patrons INNER JOIN loans ON patrons.id = loans.patron_id WHERE loans.returned_on IS null;
returned_on = Null is wrong, just a type wrong on your statement.
Chufan Xiao
18,955 Pointsshould be returned_on is null
jlampstack
23,932 PointsThis was a fun challenge, here is my solution.
SELECT p.first_name, p.last_name, p.email FROM patrons AS p
INNER JOIN loans AS l
ON p.id = l.patron_id
WHERE l.returned_on IS NULL
GROUP BY p.library_id;
I chose to group by the library id because although rare, it is still possible that one person can have more than one library card. Books are usually checked out with a library card, which in turn matches the patrons personal information.
Brane Opačič
6,686 PointsHi!
Thanks for the answer.
But doesn't the syntax supposed to be "returned_on IS null;"
Anyways, thanks for the help, much appreciated!
Jonathan Grieve
Treehouse Moderator 91,253 PointsEven better if that works. :)
I do have a blind spot when it comes to the WHERE clause but just no you do need to do that further filtering to get the records you want!
Brane Opačič
6,686 PointsWill do, thanks again Jonathan!
Riley Egan
2,452 PointsHere is what I came up with thanks to the insight from everyone above:
SELECT p.first_name, p.last_name, l.returned_on FROM patrons AS p INNER JOIN loans AS l ON p.ID = l.patron_ID WHERE l.returned_on IS null GROUP BY p.last_name;
ARCHIT RANA
Full Stack JavaScript Techdegree Graduate 27,853 PointsHere is my solution:
SELECT pt.first_name, pt.email FROM patrons AS pt INNER JOIN loans AS ln ON pt.id = ln.patron_id WHERE ln.returned_on IS NULL GROUP BY ln.patron_id;