mysql query two tables

Question

I need to query two tables like this...

table one
customers

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

etc...

table two
contacts

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

I need to query both tables.

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

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

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

Here is my current query:

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.

Answer 1

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