merge two tables please help 3

[slim2]

I would like to merge two tables and have not been able to get the desired results.
E.G.
TABLE1 TABLE2
12344 field1
12345 field1 12345 field2
12346 field1 12347 field2

to produce

12344 field1 0
12345 field1 field2
12346 field1 0
12347 0 field2

 

[tlbroadbent]

Try something like this.

Select a.ID, a.field1, Nz(b.field2,0)
Into table3
From table1 As a Inner Join table2 As b
on a.ID=b.ID
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[BNPMike]

Terry

Can you explain this? I have tested it and it just delivers the inner joined 12345, as I would expect.

I would have though you need to do an outer join one way and then outer join that back to the 'second' table to pick up the remaining missing values?

I think it is a sad and unnecessary shortcoming of SQL that you cannot do a full outer join. You can with my old friend Focus from Information Builders.
mike.stephens@bnpparibas.com

 

[tlbroadbent]

Mike,

I appreciate your pointing out the shortcomings oin my solution. I need to pay more attention to detail. That always gets me in trouble.

It is unfortunate that Access SQL lacks a full outer join. I won't hold ny breath waiting for the feature to be added as it appears that MS is moving to SQL Server (MSDE) as the standard database engine.

You can achieve a full outer join using a UNION of two outer joins.

SELECT
a.ID, nz([a].[Field1],0) AS New1,
nz(.[Field2],0) AS New2
FROM TblMain AS a LEFT JOIN TblImported AS b
ON a.ID = b.ID;
UNION
SELECT
a.ID, nz(.[Field1],0) AS New1,
nz([a].[Field2],0) AS New2
FROM TblImported AS a LEFT JOIN TblMain AS b
ON a.ID = b.ID; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[slim2]

Mike, Terry,

Thanks much for the help.