联接其他 2 个表和这些联接表中的条件上的 2 个 - Join 2 other tables and 2 on conditions within those joined tables

$result = mysql_query("SELECT DISTINCT subscriberMaster.id,    
subscriberMaster.locationCodeID, subscriberMaster.firstName,
subscriberMaster.lastName, subscriberMaster.company, subscriberMaster.address,
subscriberMaster.address2, subscriberMaster.city, subscriberMaster.province,
subscriberMaster.postalCode, subscriberMaster.country,
subscriberMaster.numLabels
FROM subscriberMaster
JOIN commodityMaster ON commodityMaster.subscriberID = subscriberMaster.id AND
commodityMaster.commodityID IN (" . $commodityquery2 . ")
JOIN industryMaster ON industryMaster.subscriberID = subscriberMaster.id AND
industryMaster.industryID IN (" . $industryquery2 . ")
WHERE subscriberMaster.locationCodeID = $locationCodeID AND
subscriberMaster.province IN (" . $provincequery2 . ") AND
(subscriberMaster.deleted = 0) AND (subscriberMaster.expiryDate = '00-00-00')
OR (subscriberMaster.expiryDate > NOW()) ORDER BY $orderBy ASC");

此查询中的所有内容都运行良好，直到我添加了第二个连接，其中它也与主表中的相同 id 匹配。Where 子句都工作正常，并且具有两个 ON 条件的第一个连接都工作正常。这似乎是第二个 JOIN 子句的问题。

基本上，订阅者主是包含所有记录详细信息（地址，电话号码等）的主表。CommodityMaster和IndustryMaster存储了有关他们从事的行业以及他们销售的商品的详细信息。

编辑**这是我回显查询时出现的结果。

SELECT DISTINCT subscriberMaster.id, subscriberMaster.locationCodeID,
subscriberMaster.firstName, subscriberMaster.lastName, subscriberMaster.company,
subscriberMaster.address, subscriberMaster.address2, subscriberMaster.city,
subscriberMaster.province, subscriberMaster.postalCode, subscriberMaster.country,
subscriberMaster.numLabels FROM subscriberMaster
JOIN commodityMaster ON commodityMaster.subscriberID = subscriberMaster.id
JOIN industryMaster ON industryMaster.subscriberID = subscriberMaster.id
WHERE subscriberMaster.locationCodeID = 1 AND commodityMaster.commodityID IN ('1',
'2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19',
'20','21','22','23','24','25','26','27','28','29','30','31','32','33') AND
industryMaster.industryID IN ('1','3','2') subscriberMaster.province IN
('Ontario') AND (subscriberMaster.deleted = 0) AND (subscriberMaster.expiryDate =
 '00-00-00') OR (subscriberMaster.expiryDate > NOW())
ORDER BY company ASC

我会将您的附加约束从 JOIN 部分移动到 WHERE 部分。这是它应该在的地方。

SELECT DISTINCT 
    subscriberMaster.id,    
    subscriberMaster.locationCodeID, 
    subscriberMaster.firstName,
    subscriberMaster.lastName, 
    subscriberMaster.company, 
    subscriberMaster.address,
    subscriberMaster.address2, 
    subscriberMaster.city, 
    subscriberMaster.province,
    subscriberMaster.postalCode, 
    subscriberMaster.country,
    subscriberMaster.numLabels
    FROM subscriberMaster
        JOIN commodityMaster 
            ON commodityMaster.subscriberID = subscriberMaster.id 
        JOIN industryMaster 
            ON industryMaster.subscriberID = subscriberMaster.id 
    WHERE subscriberMaster.locationCodeID = $locationCodeID 
        AND commodityMaster.commodityID IN (" . $commodityquery2 . ")
        AND industryMaster.industryID IN (" . $industryquery2 . ")
        AND subscriberMaster.province IN (" . $provincequery2 . ") 
        AND (subscriberMaster.deleted = 0) 
        AND (subscriberMaster.expiryDate = '00-00-00')
        OR (subscriberMaster.expiryDate > NOW()) 
    ORDER BY $orderBy ASC

将您的行业大师JOIN更改为LEFT OUTER JOIN，看看它是否返回数据，我假设您没有industrymaster中所有 subscribermaster id 的行，试试这个

SELECT DISTINCT subscriberMaster.id,    
subscriberMaster.locationCodeID, subscriberMaster.firstName,
subscriberMaster.lastName, subscriberMaster.company, subscriberMaster.address,
subscriberMaster.address2, subscriberMaster.city, subscriberMaster.province,
subscriberMaster.postalCode, subscriberMaster.country,
subscriberMaster.numLabels
FROM subscriberMaster
LEFT OUTER JOIN commodityMaster 
   ON commodityMaster.subscriberID = subscriberMaster.id 
   AND commodityMaster.commodityID IN (" . $commodityquery2 . ")
LEFT OUTER JOIN industryMaster 
   ON industryMaster.subscriberID = subscriberMaster.id 
   AND industryMaster.industryID IN (" . $industryquery2 . ")
WHERE subscriberMaster.locationCodeID = $locationCodeID AND
subscriberMaster.province IN (" . $provincequery2 . ") AND
(subscriberMaster.deleted = 0) AND (subscriberMaster.expiryDate = '00-00-00')
OR (subscriberMaster.expiryDate > NOW()) 
ORDER BY $orderBy ASC