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 trialnb11
17,552 PointsJoins??
HI,
I'm trying to figure out how to create a join that shows the remaining items not in the join from both tables in the last part of Querying Relational Databases.
The questions is: Use a JOIN to find out which patrons haven't had any loans. Select their first name and email address.
The databases are loans and patrons and here is my code:
SELECT DISTINCT first_name, email
FROM patrons
JOIN loans ON loans.patron_id = patrons.id;
It is bringing up the patrons which have had loans but I can't figure out how to bring up the ones who have not had loans, there is one I believe?
4 Answers
Steven Parker
231,184 PointsYou need an OUTER join to get the records that don't match.
The standard "join" is an inner join. This will only return records that have common values.
If you want to see records that do not have matching values, you need an outer join, further filtered by a WHERE clause for the null match. For example:
SELECT DISTINCT first_name, email FROM patrons
LEFT OUTER JOIN loans ON loans.patron_id = patrons.id
WHERE loans.patron_id IS NULL;
Here's a handy "cheat sheet" from a Code Project article for the 7 kinds of joins:
(this one is second down on the left)
nb11
17,552 PointsAh that's great thank you for your help.
I can see the JOIN
I was using was the wrong one and needed the WHERE
clause also. This is my code that gave me what I was looking for:
SELECT first_name, email
FROM patrons
LEFT JOIN loans ON loans.patron_id = patrons.id
WHERE loans.loaned_on IS NULL;
Ikechukwu Arum
5,337 PointsSELECT first_name, email, patrons.id FROM patrons LEFT JOIN loans ON patrons.id =loans.patron_id WHERE patrons.id IS NOT loans.patron_id;
Enock Addey
Courses Plus Student 10,420 PointsThank you, Steven Parker!
Erich Martin
5,141 PointsErich Martin
5,141 PointsGreat cheatsheet!
Teacher Russell
16,873 PointsTeacher Russell
16,873 PointsWhat is the significance of the double letters, Table A A, Table B B?
nb11
17,552 Pointsnb11
17,552 PointsIt's a short hand way of renaming the table for your own query, making it shorter to write.
It could have been written as 'TableA AS A' or 'TableB AS B'
So when you reference the tables in your query you just write 'A' instead of 'TableA'.
Helps alot when you have long table names