MySQL查询中的Join If Exists（如果存在则连接）

Question

我正在运行一个报告，将委员会成员的信息导出到Excel电子表格中。

以下是我的查询语句：

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

问题是部分成员没有设置位置ID，或者位置ID被设置为0，因此这些成员在报告中不会显示。

我是否可以对位置JOIN进行限定？如果成员的位置ID存在，则提取信息，否则显示可用的信息。

- Ryan

Answer 1

将你两个JOIN改为LEFT JOIN。这样，你可以得到membership_individual表中所有匹配where条件的记录，并在其他表中获取NULL值，以表示行不匹配。

- Fosco

---

是的，我觉得这很简单，我想我需要喝点咖啡。谢谢！ - Ryan

Answer 2

使用 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

- Frank Schmitt

---

“outer”是暗示的意思。只需要使用“left join”就可以了。 - Fosco

Answer 3

使用左连接，意味着您的查询将变成：

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

你将得到所有成员的数据，即使在位置表中没有行的成员也是如此。

- Nikoloff

Answer 4

尝试使用

LEFT JOIN membership_location ON membership_individual.location_id = membership_location.id 

使用代替您的变量。

- Dmitri Gudkov