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?
Steven Parker186,662 Points
You 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)
Ah 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 Arum3,495 Points
SELECT first_name, email, patrons.id FROM patrons LEFT JOIN loans ON patrons.id =loans.patron_id WHERE patrons.id IS NOT loans.patron_id;