CODEWITHSUNDEEP
mysql
Ahmad Hanifah Fidaus
Ahmad Hanifah Fidaus

Reputation: 33

mysql merge two or more select into one table

I want to combine my query below into one table but result from 'union' is merging it into one column. I want it create different column instead of one like illustrated bellow.
How do I achieve that?

I have a query like this :

(select count(*) as col_1
   from ref_A
   inner join ref_B
   on ref_A.id_A= ref_B.id_A
   inner join ref_C
   on ref_C.id_C= ref_B.id_C
   group by ref_C.id_C) 
union all

(select count(*) as col_2
   from ref_B
   inner join ref_C
   on ref_C.id_C= ref_B.id_C
   group by ref_C.id_C) 

_________________
| col_1 | col_2 |
|_______|_______|
|1      |$ 2,00 |
-----------------
|2      |$ 3,50 |
-----------------

Upvotes: 3

Views: 63

Answers (1)

Rahul
Rahul

Reputation: 18567

Try this,

(select count(*) as col_1, 'table 1' as ident
   from ref_A
   inner join ref_B
   on ref_A.id_A= ref_B.id_A
   inner join ref_C
   on ref_C.id_C= ref_B.id_C
   group by ref_C.id_C) 
union all

(select count(*) as col_1, 'table 2' as ident
   from ref_B
   inner join ref_C
   on ref_C.id_C= ref_B.id_C
   group by ref_C.id_C)

here ident which wont merge data from two queries, queries data will get merge only when there are identical rows found.

Upvotes: 1

Related Questions