Join If Exists in a MySQL query

Question

I'm running a report that exports the information for members of committees into an excel spreadsheet.

Here's my query:

SELECT membership_organization.name AS Firm, 
membership_individual.first AS FirstName, 
membership_individual.middle AS MiddleName, 
membership_individual.last AS LastName, 
membership_individual.email AS Email, 
membership_individual.phone AS Phone, 
membership_location.addr1 AS Address1, 
membership_location.addr2 AS Address2, 
membership_location.city AS City, 
membership_location.state AS State, 
membership_location.zipcode AS Zip 
FROM membership_individual 
JOIN membership_organization ON membership_individual.org_name_id = membership_organization.id 
JOIN membership_location ON membership_individual.location_id = membership_location.id 
WHERE membership_individual.id IN ({list if ids}) 
ORDER BY LastName

The problem is some of the members don't have a location id set, or it's set to 0, so those members don't show up in the report.

Is there a way I can qualify the location JOIN? If the members location id exists pull the info, if not show me the info that is available.

Answer 1

Change both of your JOINs into LEFT JOINs. You'll get all your records from membership_individual where the where clause matches, and NULL values for the other tables where rows don't match.

Answer 2

Use a LEFT OUTER JOIN:

SELECT membership_organization.name AS Firm, 
membership_individual.first AS FirstName, 
membership_individual.middle AS MiddleName, 
membership_individual.last AS LastName, 
membership_individual.email AS Email, 
membership_individual.phone AS Phone, 
membership_location.addr1 AS Address1, 
membership_location.addr2 AS Address2, 
membership_location.city AS City, 
membership_location.state AS State, 
membership_location.zipcode AS Zip 
FROM membership_individual 
JOIN membership_organization ON membership_individual.org_name_id = membership_organization.id 
LEFT OUTER JOIN membership_location ON membership_individual.location_id = membership_location.id 
WHERE membership_individual.id IN ({list if ids}) 
ORDER BY LastName

Answer 3

Use left join, meaning your query will become

SELECT membership_organization.name AS Firm, 
membership_individual.first AS FirstName, 
membership_individual.middle AS MiddleName, 
membership_individual.last AS LastName, 
membership_individual.email AS Email, 
membership_individual.phone AS Phone, 
membership_location.addr1 AS Address1, 
membership_location.addr2 AS Address2, 
membership_location.city AS City, 
membership_location.state AS State, 
membership_location.zipcode AS Zip 
FROM membership_individual 
JOIN membership_organization ON membership_individual.org_name_id = membership_organization.id 
LEFT JOIN membership_location ON membership_individual.location_id = membership_location.id 
WHERE membership_individual.id IN ({list if ids}) 
ORDER BY LastName

and you'll get the data for all members, even the ones for whom there are no rows in the location table.