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

JOINS 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
Steven Parker
231,236 Points

You're really close.

When testing for NULL, instead of the standard equality operator, you use the word "IS".

... WHERE ln.returned_on IS NULL;

I lost a day on the word IS. Wish I posted earlier when you were helping me with the other stuff.

Dwayne Pate
Dwayne Pate
12,249 Points

You've summed up my programming career in one sentence!

Oğulcan Girginc
Oğulcan Girginc
24,848 Points

Update: Ignore this answer...

~Also, I think, you are suppose to use LEFT OUTER rather than INNER for your JOIN.~

Steven Parker
Steven Parker
231,236 Points

You 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
Oğulcan Girginc
24,848 Points

Steven Parker I thought, this one was the second task, not first! My bad! :)

Couldn'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?

Or 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
Steven Parker
231,236 Points

I took the phrase "outstanding books" in the instructions to mean "loans that have not been returned".