CODEWITHSUNDEEP
mysqlsqljoin
Ryan
Ryan

Reputation: 749

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 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.

Upvotes: 20

Views: 46835

Answers (4)

Nikoloff
Nikoloff

Reputation: 4160

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.

Upvotes: 2

Dmitri Gudkov
Dmitri Gudkov

Reputation: 2133

Try to use 

LEFT JOIN membership_location ON membership_individual.location_id = membership_location.id

instead of your variant..

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30835

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

Upvotes: 5

Fosco
Fosco

Reputation: 38526

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.

Upvotes: 44

Related Questions