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

Abdullah Jassim
Abdullah Jassim
4,551 Points
Posted by Abdullah Jassim
Abdullah Jassim
4,551 Points

I want to make sure my answer is right for these three questions. (SQL PLAYGROUND - PATRONS WITH OUTSTANDING LOANS

Q1) -- Use a JOIN to select all patrons with outstanding books. Select their first name and email address.

Ans: '' SELECT patrons.first_name, patrons.email, loans.loaned_on, loans.returned_on FROM patrons INNER JOIN loans ON patrons.id = loans.patron_id WHERE loans.returned_on IS NULL;

Q2)-- Use a JOIN to find out which patrons haven't had any loans. Select their first name and email address.

Ans: SELECT first_name, email, loaned_on FROM patrons LEFT OUTER JOIN loans ON loans.patron_id = patrons.id WHERE loaned_on IS NULL;

Q3)---- Create a report that shows the title of the book, first and last name of the patron, email and all date fields of the loan.

Ans: SELECT books.title, patrons.first_name, patrons.last_name, patrons.email, loans.book_id, loans.patron_id, loans.loaned_on, loans.return_by, loans.returned_on FROM books INNER JOIN loans ON books.id = loans.book_id INNER JOIN patrons ON loans.patron_id = patrons.id;

codegritty
codegritty
5,749 Points

I'm trying to solve this myself, but I think you have Question 2 wrong.

There are no values of loaned_on that is NULL. Even if one row has a value of loaned_on that is NULL, it still doesn't answer the question of which patrons haven't had any loans. There could have been multiple books or multiple patrons who had NULL values for loaned_on.

I'll chime in further once I figure it out. Good luck.

2 Answers

Ryan Arthur
Ryan Arthur
4,910 Points
Ryan Arthur
Ryan Arthur
4,910 Points

Here's what I came up with:

Q1

SELECT DISTINCT first_name, email FROM patrons INNER JOIN loans ON patrons.id = loans.patron_id WHERE loans.returned_on IS NULL;

Q2

SELECT first_name, email FROM patrons LEFT OUTER JOIN loans ON patrons.id = loans.patron_id WHERE loans.patron_id IS NULL;

Q3 (not 100% sure on this but it looks good)

SELECT books.title, patrons.first_name || " " ||  patrons.last_name AS Name, patrons.email, loans.loaned_on, loans.return_by, loans.returned_on FROM books inner JOIN loans ON loans.book_id = books.id INNER JOIN patrons ON patrons.id = loans.patron_id ORDER BY books.title;
Che Perth
Che Perth
1,989 Points
Che Perth
Che Perth
1,989 Points

It's not accepting my first answer. This is what i have below.

SELECT first_name,email FROM patrons INNER JOIN loans on loans.id = patrons.id where returned_on IS NULL ;

I haven't gotten past the first question because it isn't accepting my answer.