Join reulst from 2 tables and when value none of them exist fill blank value
I have tables clients
andpartners
Table clients
company_id | name
------------------
11 | UA prods.
12 | CI corp.
13 | UAC
Table partners
company_id | name
------------------
11 | UA prods.
12 | CI corp.
20 | BDP Group
30 | UFF
I need to get all the company names from both tables. When a company exists in both tables, then theyr names, when they do not exist, simply leave it blank.
I've tried left joins, inner joins, but that doesn't give me what I want.
I want to get a result like this
clients | partners
--------------------
UA prods. | UA prods.
CI corp. | CI corp.
UAC |
| BDP Group
| UFF
source to share
Use this:
(select clients.name as clients, partners.name as partners from clients
LEFT JOIN partners
ON clients.company_id = partners.company_id)
UNION ALL
(select clients.name as clients, partners.name as partners from partners
LEFT JOIN clients
ON clients.company_id = partners.company_id
WHERE clients.company_id IS NULL)
source to share
You are looking for a FULL OUTER JOIN which is not supported by MySQL. But you can use LEFT JOIN and RIGHT JOIN and UNION ALL query:
select c.name as clients, p.name as partners
from
clients c left join partners p
on c.company_id = p.company_id
union all
select c.name as clients, p.name as partners
from
clients c right join partners p
on c.company_id = p.company_id
where
c.company_id is null
The first query uses a LEFT JOIN, which returns all clients and all partners when there is a match (or zero instead).
The second RIGHT JOIN will return all partners that do not have a match in the customer table.
source to share
you get this with a combination of two connections using a union as shown below
SELECT t1.c_name,t2.p_name
FROM clients t1
LEFT JOIN partners t2 ON t1.c_id = t2.p_id
UNION
SELECT t4.c_name,t3.p_name
FROM partners t3
LEFT JOIN clients t4 ON t3.p_id = t4.c_id
where t4.c_name is null
let me know if you have any problem ...
source to share
So, basically the best solution for what you are looking for is FULL OUTER JOIN
which is not supported by MySQL.
So, in mysql, you can do the same on this query -
SELECT clients.name AS clients, partners.name AS partners FROM clients
LEFT JOIN partners ON clients.company_id = partners.company_id
UNION
SELECT clients.name AS clients, partners.name AS partners FROM clients
RIGHT JOIN partners ON clients.company_id = partners.company_id
NOTE. Don't use UNION ALL
, it will give you a result like this -
clients | partners
-----------------------------------
UA prods. | UA prods.
CI corp. | CI corp.
UAC | NULL
UA prods. | UA prods.
CI corp. | CI corp.
NULL | BDP Group
NULL | UFF
source to share