UNION two tables with same primary key values

[carillonator]

I want to perform a UNION of two tables, but occasionally the primary key field in each will have the same value while the rest of the row contains different data. In this case I want only the row from table1:

table1

col1       col2
Frank     blue
Debbie   red

table2

col1        col2
George    brown
Debbie     orange

The result I want:

col1        col2
Frank      blue
Debbie     red
George    brown

 

[vbaInet]

Something like:

SELECT * table 1
UNION
SELECT * table 2

Debbie what happens to (Debbie, orange) and (Debbie, red)? Do you want distinct or all?

 

[carillonator]

When a name appears in both tables, I only want to return the row from table1.

thanks

 

[vbaInet]

Ok, that setup should work. It will select it's first record from table1.

 

[carillonator]

No, it returns both (Debbie, red) and (Debbie, orange). UNION only removes duplicates that are identical in all fields.

I think I'm going to have to do a join for one side of the union.

Ok, that setup should work. It will select it's first record from table1.

 

[SOS]

Just create a sub query for the second part of the UNION query that excludes all records from table2 where there is a value in table1. You can use the Unmatched query wizard to set it up.