isy isy - 1 month ago 4
MySQL Question

SQL : 2 columns in 1 in result set

Let's say I have 3 tables :

info_contact :

id_contact id_demand email
1 1 contact1@a.com
2 2 contact2@a.com
3 3 contact3@a.com


demand :

id_demand date
1 2016-10-12
2 2016-11-05
3 2016-12-12


invitation :

id_invitation id_demand partner_company concurrent_company
1 1 google facebook
2 1 null linkedin
3 2 google null
4 2 null yahoo
5 3 google null


I would like to have that kind of result :

Company | id_demand
----------------------
Facebook | 1
Google | 1
Google | 2
Google | 3
Linkedin | 1
Yahoo | 2


with no difference between partner_company and concurrent_company (together in the result).

For the moment I have tried :

SELECT i.partner_company, d.id_demand
FROM info_contact as c, demand as d, invitation as i
WHERE c.id_demand = d.id_demand AND d.id_demand = i.id_demand
AND i.partner_company IS NOT NULL
GROUP BY i.partner_company, d.id_demand;


and

SELECT i.concurrent_company, d.id_demand
FROM info_contact as c, demand as d, invitation as i
WHERE c.id_demand = d.id_demand AND d.id_demand = i.id_demand
AND i.concurrent_company IS NOT NULL
GROUP BY i.concurrent_company, d.id_demand;


and I don't know how to combine these 2 queries and obtain the result that I want

Answer

Try using UNION ALL

select partner_company , id_demand
From invitation 
Where partner_company is not null
Union All
select concurrent_company , id_demand
From invitation 
Where concurrent_company is not null

Also I did not JOIN the other tables since you are not selecting them. If you want to check the existence then JOIN it. Use INNER JOIN syntax to join the table

Comments