November 20, 2007 at 6:17 am
I've got two tables that I want to combine, so I can see the differences between the two tables. Here's what I've got:
Table A
SerialNo Country
0001 US
0003 US
0004 US
Table B
SerialNo Country
0001 US
0002 US
0003 Canada
Desired Results:
SerialNo TableA.Country TableB.Country
0001 US US
0002 US
0003 US Canada
0004 US
Notice how the desired results have all of the serial numbers. How do I do that?
I've tried a Union statement, and if I do a union of just the serial numbers, I do get a combined list of all the serial numbers. But if I add in the Countries, my query breaks.
Thanks,
Jim
Rochester, NY
November 20, 2007 at 6:28 am
One option is to use full outer join to get details from both the tables including missed records from either of them
See whether following sample helps
Declare @tableA Table
(SerialNo varchar(4),
Country varchar(12))
Declare @tableB Table
(SerialNo varchar(4),
Country varchar(12))
insert into @tableA values ('0001','US')
insert into @tableA values ('0003','US')
insert into @tableA values ('0004','US')
insert into @tableB values ('0001','US')
insert into @tableB values ('0002','US')
insert into @tableB values ('0003','Canada')
Select isnull(a.serialno,b.serialno) AS SerialNo,a.country,b.country
from @tableA a
full join @tableb b
on a.serialno = b.serialno
order by isnull(a.serialno,b.serialno)
November 20, 2007 at 6:33 am
This does the trick:
select isnull(a.SerialNo,b.SerialNo) as SerialNo
,a.Country as 'TableA.Country'
,b.Country as 'TableB.Country'
from Tablea a
full join Tableb b on a.SerialNo = b.SerialNo
order by isnull(a.SerialNo,b.SerialNo)
November 20, 2007 at 6:38 am
have you tried a full outer join?
November 20, 2007 at 7:32 am
Many thanks to both Rajesh Patavardhan and stewart noble who both came up with the same answer within minutes of each other. Thank you so much !
Jim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply