mysql - how do I search 2 tables when either one may be missing the join record

I have 2 tables a company and a contact table. the contact table contains the company_id which is the common field

I am writing a search and I want to return information from both tables, company name and contact name. The problem I am having is that there may a compan开发者_如何学运维y without a contact or a contact without a company. Both are legal

if I write

SELECT c.name, k.name from contact c
LEFT OUTER JOIN company k ON k.company_id = c.company id 
WHERE c.name like '%search_word%' || k.name like '%search_word%' 

I get contacts that have or don't have companies, but I don't get companies that have no contacts.

Actually my problem is worse I realize. since a company can have many contacts, there's as association table that contains the company_id and contact_id. Now I have to go from the contact to the association table to company and the Full Outer join makes no difference.

---

You're looking for a full outer join. That way, you'll get all records from both tables, joined where possible. Unfortunately, MySQL doesn't support full outer joins, so here's a solution using unions and left and right outer joins.

I have three tables, companies, contacts, and companies_contacts, where this final one is an association table. Here's the respective contents of the three:

mysql> select * from companies;

+------------+---------+
| company_id | company |
+------------+---------+
|          1 | Foo     |
|          2 | Bar     |
|          3 | Baz     |
+------------+---------+
3 rows in set (0.00 sec)

mysql> select * from contacts;
+------------+---------+
| contact_id | contact |
+------------+---------+
|          1 | Fred    |
|          2 | Barney  |
|          3 | Wilma   |
|          4 | Betty   |
+------------+---------+
4 rows in set (0.00 sec)

mysql> select * from companies_contacts;
+------------+------------+
| company_id | contact_id |
+------------+------------+
|          1 |          1 |
|          1 |          2 |
|          2 |          2 |
|          2 |          4 |
+------------+------------+
4 rows in set (0.00 sec)

The problem becomes simpler if you rethink it slightly: you want all the contacts that meet the criteria, along with the companies their associated with if possible, and you want all the companies that meet the criteria, with their associated contacts if possible. We can solve these two problems using two outer joins:

select    company, contact
from      companies
left join companies_contacts using (company_id)
left join contacts           using (contact_id)
where     company like '%B%';

And:

select     company, contact
from       companies
right join companies_contacts using (company_id)
right join contacts           using (contact_id)
where      contact like '%W%';

Using a union between these two queries will combine their results and eliminate any duplicates between the two:

select     company, contact
from       companies
left join  companies_contacts using (company_id)
left join  contacts           using (contact_id)
where      company like '%B%'
union
select     company, contact
from       companies
right join companies_contacts using (company_id)
right join contacts           using (contact_id)
where      contact like '%W%';

On the previously mentioned data, this will give the result:

+---------+---------+
| company | contact |
+---------+---------+
| Bar     | Barney  |
| Bar     | Betty   |
| Baz     | NULL    |
| NULL    | Wilma   |
+---------+---------+

Which is exactly the result you're looking for.

---

I would suggest:

SELECT c.name, k.name from contact c
FULL OUTER JOIN company k ON (k.company_id = c.company id) 
WHERE c.name LIKE '%search_word%' || k.name LIKE '%search_word%'
AND NOT(c.name is null AND k.name is null); 

So you don't get full null results.

---

select c.name, k.name
from association a
right outer join company k on k.company_id = a.company_id
right outer join contact c on c.company_id = a.company_id
where c.name like '%search_word%' || k.name like '%search_word%'