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 trialDavid Wright
4,437 PointsTask #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
Java Web Development Techdegree Graduate 22,175 PointsFirst 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...
Josh Gallagher
3,013 PointsHi 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
7,940 PointsI'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
8,586 PointsFrom 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
15,258 PointsWe 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
9,694 PointsThank 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
3,867 PointsA 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
Courses Plus Student 9,392 PointsCan 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
39,021 PointsYou 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
640 PointsI 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
Courses Plus Student 2,546 PointsRemembering 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
4,764 PointsJoining 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;
David Wright
4,437 PointsDavid Wright
4,437 PointsThank you very much Alexander!
This answer and the one from Task 2 of 3 Union and Subqueries were great detailed answers.
Oğulcan Girginc
24,848 PointsOğulcan Girginc
24,848 PointsI 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
39,021 PointsGreg Kaleka
39,021 PointsGood explanation - one quick note for anyone coming back to this: There's no need to include
loaned_on
in yourSELECT
clause. You can still use it in yourWHERE
clause. Also, I agree with Oğulcan Girginc that loans.id is a better field to filter by.