CODEWITHSUNDEEP
mysqldatabasejoin
FIFO
FIFO

Reputation: 41

select common values of multiple tables in mysql

I'm writing a php code to calculate a value. I have 4 tables with 2 column each. their headers are same. I want to fetch second column of tables that have, say 'MAN', value on their first column: here are my tables and their dummy values:

enter image description here

I joined tables on 'dst' with a where clause but the problem arises when a table has does not have a common value and we have not full outer join in mysql. I know we can simulate this with union all operation but I want a efficient way to do this. here is my try:

select t1.dst, t1.pay as pay, t2.pay as pay2, t3.pay as pay3, t4.pay as pay4 from t1 left outer join t2 on t1.dst = t2.dst left outer join t3 on t3.dst=t2.dst left outer join t4 on t1.dst=t4.dst where t1.dst='man';

it's dummy because left outer join is not true for this purpose except special cases.

actually I want this:

this

Upvotes: 0

Views: 635

Answers (1)

P.Salmon
P.Salmon

Reputation: 17655

First union then pivot

drop table if exists t,t1,t2,t3;
create table t  (dst varchar(3),value int);
create table t1 (dst varchar(3),value int);
create table t2 (dst varchar(3),value int);
create table t3 (dst varchar(3),value int);


insert into t values ('abc',10),('man',10);
insert into t1 values ('abc',10),('man',5);
insert into t2 values ('abc',10),('man',10);
insert into t3 values ('abc',10);

select  dst,
          MAX(CASE WHEN tbl = 't' then value end) as t1,
          MAX(CASE WHEN tbl = 't1' then value end) as t1,
          MAX(CASE WHEN tbl = 't2' then value end) as t2,
          MAX(CASE WHEN tbl = 't3' then value end) as t3  
from
(
select 't' as tbl,dst,value from t where dst = 'man'
union
select 't1' as tbl,dst,value from t1 where dst = 'man'
union
select 't2' as tbl,dst,value from t2 where dst = 'man'
union
select 't3' as tbl,dst,value from t3 where dst = 'man'
) s 
group by s.dst;

+------+------+------+------+------+
| dst  | t1   | t1   | t2   | t3   |
+------+------+------+------+------+
| man  |   10 |    5 |   10 | NULL |
+------+------+------+------+------+
1 row in set (0.00 sec)

Upvotes: 1

Related Questions