Join or Union between two tables? 2

[vnad]

I have two databases with similar data. I want to do a join the same tables from each database into one result set. If I do a union, everything seems to be ok except I am getting duplicate records if one field is updated in one table. For example

Table 1
id name address
10 jack 123 Vine
20 john 456 State
30 joe 678 Main

Table 2
id name address
10 jack 458 Beach
20 john 456 State
30 joe 678 Main
40 mary 999 Hubble

The result set from

select * from table1
UNION
select * from table2

is

id name address
10 jack 123 Vine
10 jack 458 Beach
20 john 456 State
30 joe 678 Main
40 mary 999 Hubble

Is there a way just to match on the id field and not every field in the table? So I would get a result set like

id name address
10 jack 123 Vine
20 john 456 State
30 joe 678 Main
40 mary 999 Hubble

????

 

[SQLSister]

And if the same ID has two addresses what criteria would you use to decide which one to choose?

 

[vnad]

I would take the record from Table2

 

[SQLSister]

Try

select * from table1 left join table2 on table1.id = table2.id where table2.id is null
UNION
select *from table2

 

[checkai]

I am assuming there is more data in the tables....otherwise this seems like you want everything out of table2 and thats it...can you further explain?

DLC

 

[vnad]

That worked! Thanks!

 

[vnad]

Little too soon...

The first part does not return any nulls so the code only returns the

select *from table2

 

[SQLSister]

if it doesn;t return any nulls then every record in table 1 has an equivalent record in table 2. SInce you said you wanted the table 2 addresses to be the ones displayed, that's all you got. In this case of course, it is simpler to just query table2 unless you expect this situation to change.

 

[checkai]

In this case of course, it is simpler to just query table2 unless you expect this situation to change.

does that mean i get a star too?