This is a sticky problem, which I almost want to say: "Do not try to do this, this is one request."
I approach SQL queries like this from a programming point of view, because I find that the results are generally less "magical." (The property that I see in too many queries seems to be SQL queries these days written using monkeys on keyboards ...)
- Indicate which company identifiers we want to list. This is a combination of these two things:
- Any "people" results correspond to a name or number.
- Any "company" results correspond to a name or number.
- List the quantity for this company and people.
First do # 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)
Since the "company_ids we want" will be a request, rebuild it to reduce it to 1 event:
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 No. 1.1:
SELECT companyid FROM contacts WHERE name = $search OR number = $search;
Part number 1.2:
SELECT id AS companyid FROM customers WHERE companyname = $search OR number = $search;
(Note that $search is our input parameterized query that is very different from one SQL provider to another, so replace this syntax as needed.)
Put the UNION of the two in IN , and we are 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 that the database can identify a query plan that will do this in a reasonable amount of time. I am sure you do not want to get to the database several times?
Pay attention to the methodology: we determined what we needed ("names / phone numbers for customers / contacts corresponding to certain companies"), and then found out the missing part ("which company is being searched for?"). This is due to the fact that, as soon as you agree with a specific person in a company (say, sam), you want everything from this company, plus the company, or all with this company identifier. Knowing this, we receive our external request (No. 2), and then we just need to figure out how to determine which companies are of interest to us.
Please note that this will not (and SQL queries, without ORDER BY, do not) query back to your rather bizarre order. However, you can add an auxiliary column to the internal query and do the following:
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 SELECT ) if you need to segment the results in all the results of this query.
(And if you end up using queries of this length, please, for the love of God, -- comment them! )