Search code examples
mysqlsqlworkbench

Union All with Two Keys in MySQL

I have two tables ('Field' and 'Wides') with the exact same columns ('HorseID' and 'RaceID') and I want to merge the tables on these columns, whilst also merging the data from both tables. My code at the moment only merges the two columns.

select field2.horseid, field2.raceid
from field2
union all
select wides2.horseid, wides2.raceid
from wides2
Solution

  • Do you want a join between the two tables?

    SELECT *
FROM field2 t1
INNER JOIN wides2 t2
    ON t2.horseid = t1.horseid AND
       t2.raceid = t1.raceid;