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

Posted by David Wright
David Wright
4,437 Points

Task #2 patrons with no loans

SELECT DISTINCT first_name, email FROM loans
  LEFT OUTER JOIN patrons ON loans.patron_ID = patrons.ID
  WHERE loaned_on IS NOT NULL;

I used this in order to get all the patrons WITH loans (4 patrons), and had to look at the patrons table in order to find out who was left out of this queried list of patrons (Craig)

How could I do this without having to look back at the patrons table?

9 Answers

Alexander Nikiforov
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,175 Points
Alexander Nikiforov
.a{fill-rule:evenodd;}techdegree seal-36
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,175 Points

First of all I think you should in first SELECT always use table without foreign_key, because otherwise, you will never get 'Craig' of this query.

Why ?

Because there may be no foreign_key related to 'Craig', and this way you will never get him.

Do you see it?

So in order to write correct query we change order:

SELECT first_name, email, loaned_on
FROM patrons
LEFT OUTER JOIN loans
ON loans.patron_ID = patrons.ID;

This way we just select patrons with their loans. And in order to find people that have no loans, we have to write

SELECT first_name, email, loaned_on
FROM patrons
LEFT OUTER JOIN loans
ON loans.patron_ID = patrons.ID
WHERE loaned_on IS NULL;

And we certainly should not use DISTINCT in this case, because we don't need it here...

David Wright
David Wright
4,437 Points

Thank you very much Alexander!

This answer and the one from Task 2 of 3 Union and Subqueries were great detailed answers.

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

I wonder, if using

WHERE loans.id IS NULL

is considered a 'better practice' compared to using

WHERE loaned_on IS NULL

for this example?! ?

Greg Kaleka
Greg Kaleka
39,021 Points
Greg Kaleka
Greg Kaleka
39,021 Points

Good explanation - one quick note for anyone coming back to this: There's no need to include loaned_on in your SELECT clause. You can still use it in your WHERE clause. Also, I agree with Oğulcan Girginc that loans.id is a better field to filter by.

SELECT first_name, email
FROM patrons
LEFT OUTER JOIN loans
ON loans.patron_ID = patrons.ID
WHERE loans.id IS NULL;
Josh Gallagher
Josh Gallagher
3,013 Points
Josh Gallagher
Josh Gallagher
3,013 Points

Hi everybody,

My solution to this is:

select p.first_name, p.email, count(l.patron_id) as rentals 
  from patrons as p
  left outer join loans as l
  on p.id = l.patron_id
  group by p.first_name
  having rentals = 0;

I though i'd share my solution for someone who might stumble upon this.

Alexander Nikiforov answer is very good, but my solution is a bit different and provides more flexibility when changing the clause to get different values.

Josh

Digital Marketer
Digital Marketer
7,940 Points
Digital Marketer
Digital Marketer
7,940 Points

I'd love any feedback as to if this is better or not. I know it's right since it pulled the right answer, and seems a little simpler than working off of the loaned_on column.

SELECT pt.id, pt.first_name AS Name, pt.email AS Email FROM patrons AS pt LEFT OUTER JOIN loans AS ln ON pt.id = ln.patron_id WHERE ln.patron_id IS NULL;

Michael Heister
Michael Heister
8,586 Points

From my understanding any of the ln. columns that will never have NULL values are valid checks. With the exception of "returned_on" any other columns SHOULD be filled if a valid loan took place.

Given that Craig didn't loan anything, the whole row will be empty in the right table. So checking for id, book_id, patron_id, loaned_on and return_by are all NULL for him. returned_on is ambiguous since it can be NULL for other loaners too.

I concur with your assessment and also went for the patron_id check, since it felt more "right", but maybe checking for the primary key would be the best answer, since it prevents accidental NULLs from corrupted columns?

Luke Vaughan
Luke Vaughan
15,258 Points
Luke Vaughan
Luke Vaughan
15,258 Points

We need to check and see what is present in one table and not present in the other. The Loaned_On contains no "NULL" values. So looking at the left outer table which is the patrons table you would want to find what "Id" in the patrons column is not present in the "Patron_id" column in the loans table.

I guess there are many ways to fix a problem but I used this. 

Select pt.first_name, pt.email, pt.id, ln.patron_id
from patrons as "pt"
 left outer JOIN loans 
 as "ln" on pt.id = ln.patron_id 
 where ln.patron_id is null;

I included the pt.id and ln.patron_id columns for reference. Hope this helps

Ricardo Ferreira
Ricardo Ferreira
9,694 Points

Thank you Luke, explaining what we are looking for here really helped clarify things as to what was happening and what we are looking for. Just clicked.

Thanx man!

Fredric von Stange
Fredric von Stange
3,867 Points
Fredric von Stange
Fredric von Stange
3,867 Points

A slight twist on this task - find all patrons who don't have any current loans. This will return both Michael and Craig.

SELECT first_name, email, count(loaned_on), count(returned_on), count(loaned_on)-count(returned_on) as OutstandingBooks 
FROM patrons as p
LEFT OUTER JOIN loans as l
ON p.id = l.patron_id
GROUP BY first_name
HAVING OutstandingBooks = 0;
Hjörtur Pálmi Pálsson
PLUS
Hjörtur Pálmi Pálsson
Courses Plus Student 9,392 Points
Hjörtur Pálmi Pálsson
Hjörtur Pálmi Pálsson
Courses Plus Student 9,392 Points

Can anyone explain to me why this logic isn't correct?

SELECT patrons.first_name, patrons.email 
FROM patrons 
LEFT OUTER JOIN loans 
ON loans.patron_id = patrons.ID 
WHERE loans.patron_id != patrons.ID;
Greg Kaleka
Greg Kaleka
39,021 Points
Greg Kaleka
Greg Kaleka
39,021 Points

You should probably post a separate question in the community, but...

You're doing an outer join on loans.patron_id and patrons.ID, so you'll have a bunch of matches, and then a bunch of records from patrons where there's no loan match. Your where clause will only affect rows that match, since you're comparing the two tables, and since you matched on what you're negating, your where clause won't do anything at all.

Besides that, the logic is backwards. What your WHERE clause is (sort of) attempting to do is find loans with no patrons. Instead you want patrons with no loans. You want records where patron.loan_id is not present, meaning it didn't match with any loan records.

It may be helpful to remove the where clause and then look at the results to understand the records you're trying to extract.

Ahmed SanaUllah
Ahmed SanaUllah
640 Points
Ahmed SanaUllah
Ahmed SanaUllah
640 Points

I tried this using a subquery and the NOT IN keyword. :)

SELECT first_name, email FROM patrons AS p LEFT OUTER JOIN loans AS l ON p.id = l.patron_id WHERE p.id NOT IN (SELECT patron_id FROM loans);

The inner query first selects all the patrons' ids from the loans table (there are 4 patrons who have borrowed books)

The outer query then selects the first name and email of the patrons who aren't in this set of 4 patrons (aka Craig).

Marcus Grant
PLUS
Marcus Grant
Courses Plus Student 2,546 Points
Marcus Grant
Marcus Grant
Courses Plus Student 2,546 Points

Remembering that when using a left join, anything that doesn't match in the right table will appear as NULL, here is my solution:

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

sadat sani
sadat sani
4,764 Points
sadat sani
sadat sani
4,764 Points

Joining this conversation to compare my answer with the answers posted here. I used the INNER JOIN because CRAIG has no foreign key in the loans table. So using INNER or OUTER LEFT JOIN will return the same result. Another thing i noticed is people are using the loaned_on column instead of the returned_on column. All patrons have the loaned_on column filled because they were all loaned books but not all of them have returned the books loaned to them.

this is my code to compare:

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 NOT NULL GROUP BY Pt.first_name;