mysql query two tables

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

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. Figure out which company IDs we want to list. This is the union of these two things:
   1. Any "people" results matched on name or number
   2. Any "company" results matched on name or number
2. List out the number for that company, and the people as well.

Let's do #2 first:

    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)

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)

Now, to fill in the fun part, we need to answer #1:

Part #1.1 is:

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

Part #1.2 is:

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

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

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?

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.

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
;

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.

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