加入如果存在于 MySQL 查询中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6279620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join If Exists in a MySQL query
提问by Ryan
I'm running a report that exports the information for members of committees into an excel spreadsheet.
我正在运行一份报告，将委员会成员的信息导出到 Excel 电子表格中。
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.
问题是一些成员没有设置位置 ID，或者它被设置为 0，所以这些成员没有出现在报告中。
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.
有没有办法让我有资格加入这个位置？如果成员位置 ID 存在，则拉取信息，如果不存在，则向我显示可用的信息。
回答by Fosco
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.
将您JOIN的两个s都更改为LEFT JOINs。您将从membership_individual 获取where 子句匹配的所有记录，以及行不匹配的其他表的NULL 值。
回答by Frank Schmitt
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
回答by Nikoloff
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.
并且您将获得所有成员的数据，即使是位置表中没有行的成员。
回答by Dmitri Gudkov
Try to use
尝试使用
LEFT JOIN membership_location ON membership_individual.location_id = membership_location.id
instead of your variant..
而不是你的变种..
