CODEWITHSUNDEEP
mysqlsqljoin
isy
isy

Reputation: 563

SQL : 2 columns in 1 in result set

Let's say I have 3 tables :

info_contact :

 id_contact     id_demand       email 
     1             1        [email protected]
     2             2        [email protected]
     3             3        [email protected]

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

Upvotes: 1

Views: 43

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 4

Related Questions