我需要像这样查询两个表...

表一
客户

id     companyname     phone  
1   |  microsoft    |  888-888-8888
2   |  yahoo        |  588-555-8874
3   |  google       |  225-558-4421

等...

表二
联系人

id     companyid    name    phone
1   |      1     |  sam   |  558-998-5541
2   |      1     |  john  |  558-998-1154
3   |      3     |  larry |  111-145-7885
4   |      3     |  dave  |  558-998-5254
5   |      2     |  sam   |  558-997-5421

我需要查询两个表。

所以如果我搜索 sam 它应该返回一个包含联系人的公司列表

microsoft  888-888-8888
      sam  558-998-5541
     john  558-998-1154
yahoo      588-555-8874
      sam  558-997-5421

因此它会返回所有与 sam 相关的公司及其所有联系人.... 同样，如果我搜索“microsoft”，它应该在没有 yahoo 的情况下返回

microsoft 888-888-8888
     sam  558-998-5541
     john 558-998-1154

如果我搜索“558-998-1154”，它应该像这样返回...

 microsoft 888-888-8888
      sam  558-998-5541
      john 558-998-1154

我希望这是清楚的......

这是我当前的查询:

SELECT * FROM 
customers, customer_contacts 
WHERE (customers.code LIKE '%#URL.qm1#%' 
       or customers.COMPANY LIKE '%#URL.qm1#%' 
       or customers.phone LIKE '%#URL.qm1#%' 
       or customers.contact LIKE '%#URL.qm1#%' 
       or customers.name LIKE '%#URL.qm1#%' 
       or customers.address1 LIKE '%#URL.qm1#%') 
AND (customers.id = customer_contacts.cid 
       and customer_contacts.deleted = 0)

这只返回那些有联系方式的人......

我需要 它也可以返回没有联系人的那些。

最佳答案

这是一个棘手的问题，我几乎想说“不要尝试这样做是一个查询”。

我从编程的角度处理这样的 SQL 查询，因为我觉得结果往往不那么“神奇”。 (我在太多查询中看到的一个属性——现在的 SQL 查询似乎是用键盘上的猴子写的……)

1. 找出我们要列出的公司 ID。这是这两件事的联合:
   1. 任何名字或号码匹配的“人物”结果
   2. 名称或编号匹配的任何“公司”结果
2. 列出该公司的电话号码，以及人员。

让我们先做#2:

    SELECT
        companyname AS name,
        phone
    FROM
        customers
    WHERE id IN (company_ids we want)
UNION
    SELECT
        name, phone
    FROM
        contacts
    WHERE companyid IN (company_ids we want)

由于“company_ids we want”将是一个查询，因此重新排列它以将其归结为仅出现 1 次:

SELECT
    name, phone
FROM
    (
        SELECT
            id AS companyid,
            companyname AS name,
            phone
        FROM
            customers
    UNION
        SELECT companyid, name, phone FROM contacts
    ) AS entities
WHERE
    companyid IN (company_ids we want)

现在，为了填补有趣的部分，我们需要回答#1:

第 1.1 部分是:

SELECT companyid FROM contacts WHERE name = $search OR number = $search;

第 1.2 部分是:

SELECT id AS companyid FROM customers WHERE companyname = $search OR number = $search;

(请注意，$search 是我们的输入 — 参数化查询因不同的 SQL 供应商而大不相同，因此请根据需要替换该语法。)

将这两个的 UNION 放入 IN 中，我们就完成了:

SELECT
    name, phone
FROM
    (
        SELECT
            id AS companyid,
            companyname AS name,
            phone
        FROM
            customers
    UNION
        SELECT companyid, name, phone FROM contacts
    ) AS entities
WHERE
    companyid IN (
            SELECT companyid FROM contacts WHERE name = $search OR phone = $search
        UNION
            SELECT id AS companyid FROM customers WHERE companyname = $search OR phone = $search
    )
;

并祈祷数据库能够在合理的时间内找出执行此操作的查询计划。您确定不想往返数据库几次？

注意方法:我们确定了我们想要的(“客户的姓名/电话/与某些公司 ID 匹配的联系人”)，然后找出缺失的部分(“哪些公司 ID？”)。这是因为一旦您匹配公司中的某个特定人员(例如，sam)，您就会想要该公司的每个人，再加上该公司，或者所有具有该公司 ID 的人。知道这一点后，我们得到了外部查询 (#2)，然后我们只需要弄清楚如何确定我们对哪些公司感兴趣。

请注意，这些不会(和 SQL 查询，没有 ORDER BY 不会)以您相当奇特的顺序返回查询。但是，您可以向内部查询添加一个辅助列，并完成此操作:

SELECT
    name, phone
FROM
    (
        SELECT
            0 AS is_person,
            id AS companyid,
            companyname AS name,
            phone
        FROM
            customers
    UNION
        SELECT 1 AS is_person, companyid, name, phone FROM contacts
    ) AS entities
WHERE
    companyid IN (
            SELECT companyid FROM contacts WHERE name = $search OR phone = $search
        UNION
            SELECT id AS companyid FROM customers WHERE companyname = $search OR phone = $search
    )
ORDER BY
    companyid, is_person, name
;

如果您需要将结果分段以获取此查询的结果，您还可以使用 is_person 列(如果您将其添加到 SELECT)。

(如果您最终使用了这么长的查询，请看在上帝的份上，-- 评论它们!)

关于mysql查询两张表，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/8989824/