isy isy - 5 months ago 20
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