mysql - SQL JOIN 查询不起作用

Question

本题涉及5张表:

[contact master]
    id (pk)
    title
    fname
    lname
    country

[home address]
    id (pk)
    contact_id (fk)
    hmcountry
    ...

[office address]
    id (pk)
    contact_id (fk)
    off_country
    ...

[category master]
    id (pk)
    name

[category to contacts]
    id (pk)
    catid
    contactid

[以下查询返回 0 行]

select 
      c1.id, 
      title, 
      fname, 
      lname, 
      c1.country as country, 
      c4.hmcountry as hmcountry, 
      c5.off_country as off_country 
   from 
      contacts
         join contact_to_categories c2 on c2.contactid=c1.id
         join `contact_address` c4 ON c4.`contact_id` = c1.`id` 
         join `contact_offices` c5 ON c5.`contact_id` = c1.`id`
   where 
          c2.catid=2 
      and ( c1.country like '%Korea, North%' 
         or c4.hmcountry like '%Korea, North%' 
         or c5.off_country like '%Korea, North%' )

[其中以下工作正常并返回预期结果]

SELECT 
      `contact_id` 
   FROM 
      `contact_address` 
   WHERE 
          `hmcountry` like '%Korea, North%' 
      and `contact_id` in (select `contactid` 
                              from `contact_to_categories` 
                             where `catid` in(2,3,6) )

[甚至这也行得通]

SELECT 
      `contact_id` 
   FROM 
      `contact_offices` 
   WHERE 
          `off_country` like '%Korea, North%' 
      and `contact_id` in ( select `contactid` 
                               from `contact_to_categories` 
                              where `catid` in(2,3,6) )

对我做错了什么有什么建议吗？

我想做的是在主要联系人主表或办公室地址或家庭地址中找到所有以“Korea, North”为国家/地区的联系人。我尝试过 INNER JOIN、LEFT JOIN 等，但运气不好。请帮忙!

提前感谢您的帮助。

Answer 1

这里有几种可能性。

第一个可能是显而易见的，我怀疑是这样，但在您的第一个查询中您有:

WHERE   c2.catid = 2 

在接下来的两个查询中，您有:

WHERE `catid` IN (2, 3, 6)

有没有可能这两个带回来的行都在CatID 3和6？这可以解释为什么您的第一个查询没有返回任何结果。

第二个更可能的概率(假设如果您使用 WHERE CatID = 2，您的第二个和第三个查询仍然返回记录)是没有联系人在朝鲜有家庭住址但也有办公地址，或者在朝鲜有办公地址但也有家庭住址。

我不确定您使用 LEFT JOIN 尝试了什么，但我不明白如果您的两个较小的查询都返回结果，为什么这不会返回结果。

SELECT  c1.id, 
        title, 
        fname, 
        lname, 
        c1.country as country, 
        c4.hmcountry as hmcountry, 
        c5.off_country as off_country 
FROM    contacts
        INNER JOIN contact_to_categories c2 
            ON c2.contactid = c1.id
        LEFT JOIN `contact_address` c4 
            ON c4.`contact_id` = c1.`id` 
        LEFT JOIN `contact_offices` c5 
            ON c5.`contact_id` = c1.`id`
WHERE   c2.catid = 2 
AND (   c1.country LIKE '%Korea, North%' 
    OR  c4.hmcountry LIKE '%Korea, North%' 
    OR  c5.off_country LIKE '%Korea, North%' 
    );