Union All with Two Keys in MySQL-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

you can use DISTINCT keyword in with select to get the unique result set.

SELECT DISTINCT t1.horseid,t1.raceid....<col name> FROM <table>