mysql - How to get combined column values from multiple tables with different columns -

mysql - How to get combined column values from multiple tables with different columns -

i trying combine multiple columns 3 tables. using union keyword feeling query utilize not efficient

for example:

create table tbl1 (id int, deed varchar(50), stk varchar(50), cost int, vol int, amt float); insert tbl1 values (1, 'a1', 's1', 10, 5, 50), (2, 'a1', 's2', 5, 5, 25), (3, 'a2', 's1', 15, 3, 45), (4, 'a2', 's2', 20, 2, 40), (5, 'a2', 's2', 20, 2, 40); create table tbl2 (id int, tid int, cost int, vol int, amt float); insert tbl2 values (1, 1, 5, 3, 15),(2, 1, 5, 1, 5),(3, 1, 15, 1, 15), (4, 2, 5, 3, 15),(5, 2, 6, 2, 12); create table tbl3 (id int, deed varchar(10), type int, amt float); insert tbl3 values (1, 'a1', 0, 10),(2, 'a1', 1, 15), (3, 'a2',1, 5),(4, 'a3',0, 5);`

query used

select act,stk,amtfrom tbl1 union select (select deed tbl1 tbl2.tid = tbl1.id) amt, (select stk tbl1 tbl2.tid = tbl1.id) stk, amt tbl2

is there way same without using inner select queries twice? please give me efficient query?

here fiddle

expected output (amt 3 tables act='a1')

act stk amt a1 s1 50 a1 s2 25 a1 s1 15 a1 s1 5 a1 s1 15 a1 s1 10 a1 s1 15

just utilize explicit join:

select act, stk, amt tbl1 union select t1.act amt, t1.stk, t2.amt tbl2 bring together tbl1 on tbl2.tid = tbl1.id;

mysql