mysql - MySQL仅在现有行上加入

我有以下查询

SELECT custconcompany, custconfirstname, custconlastname, custconemail, custconphone, shipaddress1, shipaddress2, shipcity, stateabbrv, shipzip, countryname, websitecheck.formfieldfieldvalue websitevalue, excludecheck.formfieldfieldvalue excludevalue
            FROM obcisc_customers
            JOIN ( (obcisc_shipping_addresses JOIN obcisc_countries
                    ON obcisc_shipping_addresses.shipcountryid = obcisc_countries.countryid)
                LEFT JOIN obcisc_country_states
                    ON obcisc_shipping_addresses.shipstateid = obcisc_country_states.stateid
                LEFT JOIN obcisc_formfieldsessions websitecheck
                    ON obcisc_shipping_addresses.shipformsessionid = websitecheck.formfieldsessioniformsessionid
                LEFT JOIN obcisc_formfieldsessions excludecheck
                    ON obcisc_shipping_addresses.shipformsessionid = excludecheck.formfieldsessioniformsessionid)
ON obcisc_customers.customerid = obcisc_shipping_addresses.shipcustomerid
            WHERE custgroupid = 11
            AND websitecheck.formfieldfieldid = 24
            AND excludecheck.formfieldfieldid = 30
            AND excludecheck.formfieldfieldvalue != 'a:1:{i:0;s:3:"Yes";}'
            ORDER BY shipstate, shipcity

效果很好，除了我还需要它返回不存在“ excludecheck.formfieldfieldid = 30”的行...现在它不返回它们

最佳答案

编写LEFT JOIN时，要与之连接的表上的所有条件都应放在ON子句中。如果将其放入WHERE子句，则将过滤掉第二张表中没有匹配行的第一张表中的结果，因为来自外部联接的NULL值将不匹配标准。

SELECT custconcompany, custconfirstname, custconlastname, custconemail, custconphone, shipaddress1, shipaddress2, shipcity, stateabbrv, shipzip, countryname, websitecheck.formfieldfieldvalue websitevalue, excludecheck.formfieldfieldvalue excludevalue
FROM obcisc_customers
JOIN obcisc_shipping_addresses
    ON obcisc_customers.customerid = obcisc_shipping_addresses.shipcustomerid
JOIN obcisc_countries
    ON obcisc_shipping_addresses.shipcountryid = obcisc_countries.countryid)
LEFT JOIN obcisc_country_states
    ON obcisc_shipping_addresses.shipstateid = obcisc_country_states.stateid
LEFT JOIN obcisc_formfieldsessions websitecheck
    ON obcisc_shipping_addresses.shipformsessionid = websitecheck.formfieldsessioniformsessionid
       AND websitecheck.formfieldfieldid = 24
LEFT JOIN obcisc_formfieldsessions excludecheck
    ON obcisc_shipping_addresses.shipformsessionid = excludecheck.formfieldsessioniformsessionid
       AND excludecheck.formfieldfieldid = 30
       AND excludecheck.formfieldfieldvalue != 'a:1:{i:0;s:3:"Yes";}')
WHERE custgroupid = 11
ORDER BY shipstate, shipcity

另一种方法是将(excludecheck.formfieldfieldid = 30 OR excludecheck.formfieldfieldid IS NULL)放在WHERE子句中。但这更加冗长，而且我认为MySQL难以优化，特别是如果您有多个要像这样联接的表。