CODEWITHSUNDEEP
mysqlsql
user2168066
user2168066

Reputation: 635

Return union query into multiple columns

I have 4 different select statemnts that union the results and display the orders like so.

lab_orders              MED111
lab_orders              MED262383
mg_orders               MED262383
mg_orders               UNT222
pcg_pharmacogenetics    MED262371
pcg_pharmacogenetics    UNT248234
well_wellness           UST601061
well_wellness           UNTAccession
well_wellness           UST333

Using this query.

select 'mg_orders',csv as csv1
from (select csv from csvdata where csv not in(select orderid from mg_orders)) as t1
union
select 'lab_orders',csv as csv2
from (select csv from csvdata where csv not in(select orderid from lab_orders)) as t2
union
select 'pcg_pharmacogenetics',csv as csv3
from (select csv from csvdata where csv not in(select orderid from pcg_pharmacogenetics)) as t3
union
select 'well_wellness',csv as csv4
from (select csv from csvdata where csv not in(select orderid from well_wellness)) as t4

What I want to do is return the result set in separate columns, I have tried the following but get "no column named csv2" 

select csv1, csv2, csv3, csv4
from(
select 'mg_orders',csv as csv1
from (select csv from csvdata where csv not in(select orderid from mg_orders)) as t1
union
select 'lab_orders',csv as csv2
from (select csv from csvdata where csv not in(select orderid from lab_orders)) as t2
union
select 'pcg_pharmacogenetics',csv as csv3
from (select csv from csvdata where csv not in(select orderid from pcg_pharmacogenetics)) as t3
union
select 'well_wellness',csv as csv4
from (select csv from csvdata where csv not in(select orderid from well_wellness)) as t4
) as t


   lab_orders       mg_orders     pcg_pharmacogenetics            
   MED111           MED262383     MED262371
   MED262383        UNT222        UNT248234

Upvotes: 0

Views: 314

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

When you are using union, the column-names or aliases from the first query would be used for the result-set. In your case the result set would contain mg_orders and csv1 as column headers.

I assume you are looking for a query similar to

select 
max(case when csv not in(select orderid from mg_orders) then csv end) as csv1,
max(case when csv not in(select orderid from lab_orders) then csv end) as csv2,
max(case when csv not in(select orderid from pcg_pharmacogenetics) then csv end) as csv3,
max(case when csv not in(select orderid from well_wellness) then csv end) as csv4
from csvdata

Upvotes: 1

Related Questions