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
Stephen Funk
Courses Plus Student 2,650 PointsJOINS Review & Practice
-- Use a JOIN to select all patrons with outstanding books. Select their first name and email address. loans table columns: id, patron_id, book_id, loaned_on, return_by, returned_on. patrons table columns: id, first_name, email
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 = NULL;
I keep getting errors that no RIGHT and FULL OUTER JOINS are in this pre-programmed objective. The only criteria that both tables share are IDs.
Do I need a sub-query? I have wasted about about a day.
4 Answers
Steven Parker
229,732 PointsYou're really close.
When testing for NULL, instead of the standard equality operator, you use the word "IS".
... WHERE ln.returned_on IS NULL;
Stephen Funk
Courses Plus Student 2,650 PointsI lost a day on the word IS. Wish I posted earlier when you were helping me with the other stuff.
Dwayne Pate
12,249 PointsYou've summed up my programming career in one sentence!
Oğulcan Girginc
24,848 PointsUpdate: Ignore this answer...
~Also, I think, you are suppose to use LEFT OUTER rather than INNER for your JOIN.~
Steven Parker
229,732 PointsYou are told to "select all patrons with outstanding books". Since having outstanding books guarantees that there will be a record in the loans table for the patron, then you don' t need a LEFT OUTER join here.
Oğulcan Girginc
24,848 PointsSteven Parker I thought, this one was the second task, not first! My bad! :)
Luke Vaughan
15,258 PointsCouldn't you use
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 > ln.return_by;
No need for is NULL right?
Luke Vaughan
15,258 PointsOr is it asking to check and see who has not returned their book?
I am reading it as select all patrons that have a book that was returned late.
Steven Parker
229,732 PointsI took the phrase "outstanding books" in the instructions to mean "loans that have not been returned".