mysql查询两个表 [英] mysql query two tables

问题描述

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

I need to query two tables like this...

表一
客户

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

我需要查询两个表.

因此，如果我搜索山姆 它应返回带有联系人的公司列表

So if I search for sam it should return a list of companies with the contacts

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

因此它将返回所有拥有sam的公司以及与其联系的所有人员.... 同样，如果我要搜索"microsoft"，它应该不带Yahoo就返回

so it returns all company with sam and all contacts with it.... same is if I would search 'microsoft' it should return without yahoo

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

如果我搜索"558-998-1154"，它应该会这样返回...

and if I search "558-998-1154" it should return like this...

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

我希望这很清楚....

I hope this is clear....

这是我当前的查询:

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)

这仅返回有联系的人...

this returns only those who have a contact...

我需要 它也返回没有联系的人.

I would need it to return the ones without contacts as well.

推荐答案

这是一个棘手的问题，我几乎要说不要尝试执行一个查询".

This is a sticky problem, one that I almost want to say "don't try to do this is one query".

从编程的角度来看，我这样处理SQL查询，因为我觉得结果往往不太神奇". (我在太多查询中看到的一个属性-看来这些天的SQL查询是使用键盘上的猴子编写的……)

I approach SQL queries like this from a programming perspective, as I feel the results tend to be less "magical". (A property I see in too many queries — it seems SQL queries these days are written using monkeys at keyboards…)

1. 找出我们要列出的公司ID.这是这两件事的结合:

1. Figure out which company IDs we want to list. This is the union of these two things:

1. 任何人"的姓名或电话号码匹配的结果
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"将成为查询，因此将其重新排列以将其简化为仅出现1次:

Since "company_ids we want" is going to be a query, rearrange this to boil it down to just 1 occurrence:

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:

Now, to fill in the fun part, we need to answer #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供应商之间都存在很大差异，因此请根据需要替换该语法.)

(Note that $search is our input — parameterized queries differ greatly from one SQL vendor to the next, so replace that syntax as needed.)

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

Put the UNION of those two in the IN, and we're done:

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
    )
;

并祈祷数据库可以找出一个查询计划，以在合理的时间内执行该查询计划.确定不想多次往返数据库吗?

And pray the database can figure out a query plan that performs this in a reasonable amount of time. Sure you don't want to roundtrip to the DB a few times?

请注意方法:我们确定了我们想要的(与某些公司ID相匹配的客户/联系人的姓名/电话")，然后找出缺失的部分(哪些公司ID?").这是由于以下事实:一旦您匹配公司中的某个特定人员(例如sam)，您就希望该公司的所有人，加上该公司或具有该公司ID的所有内容.知道了这一点，我们得到了外部查询(#2)，然后只需要弄清楚如何确定我们对哪些公司感兴趣.

Note the methodology: We determined what we wanted ("the names/phones for customers/contacts matching certain companyids") and then figured out the missing piece ("which company ids?"). This comes from the fact that once you match a particular person in a company (say, sam), you want everyone from that company, plus the company, or everything with that company ID. Knowing that, we get our outer query (#2), and then we just need to figure out how to determine which companies we're interested in.

请注意，这些命令(如果没有ORDER BY，则不会执行SQL查询)会以您看中的顺序返回查询.您可以在内部查询中添加一个帮助器列，并完成此操作:

Note that these won't (and SQL queries, without an ORDER BY don't) give the queries back in your rather fancy order. You can add a helper column to the inner query, however, and accomplish this:

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).

You can also use the is_person column (if you add it to the SELECT) if you need to segment the results in whatever gets this query's results.

(如果您最终使用这种长度的查询，请，出于对上帝的爱，-- comment them!)

(And if you end up using queries of this length, please, for the love of God, -- comment them!)

这篇关于mysql查询两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！