I have multiple tables trying to get values from all tables using UNION ALL and LEFT JOIN in MySql the query I used look like bellow.
(SELECT
t1.name, t2.cat, t3.utt, t4.gcost, t4.net
FROM table_one as t1
LEFT JOIN table_three as t2
ON t2.id = t1.ct
LEFT JOIN table_four as t3
ON t3.id = t1.ut
LEFT JOIN table_five as t4
ON t4.gid = t1.id
) UNION ALL
(SELECT
t1.name, t2.cat, t3.utt, t4.gcost, t4.net
FROM table_tow as t1
LEFT JOIN table_three as t2
ON t2.id = t1.ct
LEFT JOIN table_four as t3
ON t3.id = t1.ut
LEFT JOIN table_five as t4
ON t4.gid = t1.id
)
but the above query return double values in MySQL
here is fiddle link: http://sqlfiddle.com/#!9/bd30ba/1
any help would be appreciated.
thanks
UPDATE
as per P.Salmon question to know the complete logic the above query is using for Fixed assets App, so fixed assets has multiple item values like:
so the query in fiddle there are 2 different price/amount the query should return both values for each reference row id.
Thank you everyone for helping but none of these helped me, I know there are repeated ID's in table_five, from table_one and table_tow... the only way I solve my problem I add new sequence ID in table_one, table_tow ... AS
seq_.time();
same sequence adding in table_five during insert.. after that I changed the query to.
(SELECT
t1.name, t2.cat, t3.utt, t4.gcost, t4.net
FROM table_one as t1
LEFT JOIN table_three as t2
ON t2.id = t1.ct
LEFT JOIN table_four as t3
ON t3.id = t1.ut
LEFT JOIN table_five as t4
ON t4.gid = t1.id
AND t4.seq = t1.seq
) UNION ALL
(SELECT
t1.name, t2.cat, t3.utt, t4.gcost, t4.net
FROM table_tow as t1
LEFT JOIN table_three as t2
ON t2.id = t1.ct
LEFT JOIN table_four as t3
ON t3.id = t1.ut
LEFT JOIN table_five as t4
ON t4.gid = t1.id
AND t4.seq = t1.seq
)
now I get exact data as I want. thanks