Search code examples
mysqlinnodb

MySQL UNION ALL with LEFT JOIN returning double values


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:

  1. actual price.
  2. Accumulated Depreciation
  3. Current Value
  4. ...

so the query in fiddle there are 2 different price/amount the query should return both values for each reference row id.


Solution

  • 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