Join If Exists in a MySQL query
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 ha开发者_运维知识库ve 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.
Change both of your JOIN
s into LEFT JOIN
s. 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.
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
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.
Try to use
LEFT JOIN membership_location ON membership_individual.location_id = membership_location.id
instead of your variant..
精彩评论