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

George May
George May
3,324 Points
Posted by George May
George May
3,324 Points

Answers to Review and Practice

Can someone check if my queries for this exercise are correct? Or is there another way to find out the answers?

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

SELECT first_name, email FROM patrons JOIN loans ON patron_id WHERE returned_on IS NULL;

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

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

Task 3 -- 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.

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

3 Answers

Mark Chesney
Mark Chesney
11,747 Points
Mark Chesney
Mark Chesney
11,747 Points

On question #3, I made my joins more explicit. I welcome anyone's feedback:

SELECT books.title
, patrons.first_name || ' ' || patrons.last_name AS full_name
, patrons.email
, loans.loaned_on, loans.return_by, loans.returned_on
FROM books
LEFT OUTER JOIN loans
ON loans.book_id = books.id
INNER JOIN patrons
ON patrons.id = loans.patron_id;
Michael Pashkov
Michael Pashkov
22,024 Points
Michael Pashkov
Michael Pashkov
22,024 Points

Hi! check out task 1 -- Use a JOIN to select all patrons with outstanding books. Select their first name and email address.

select 
p.first_name, 
p.email,
l.book_id,
l.loaned_on,
l.returned_on
from patrons as p
inner join loans as l on l.patron_id = p.id
where l.returned_on is null;

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

select 
p.first_name, 
p.email,
l.book_id,
l.loaned_on,
l.return_by
from patrons as p
inner join loans as l on l.patron_id = p.id
where l.returned_on is not null group by p.email;

task 3

select DISTINCT 
b.title,
p.first_name, 
p.email,
P.last_name,
l.loaned_on,
l.return_by,
l.returned_on
from books as b
inner join loans as l on l.book_id = b.id
inner join patrons as p on l.patron_id = p.id;
justlevy
justlevy
6,325 Points
justlevy
justlevy
6,325 Points

Can someone please help me understand the logic of WHERE xyz IS NULL? Am I correct in thinking it is data missing when you combine the tables (left join)?

WHERE loans.id IS NULL;

CODE BLOCK - 2nd Example

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