Magic Select Statement....

[jane30]

Hi, I have two tables A, B with a common field DeptNo. Can I use one magic select statement to select the deptNo that both have, the DeptNO A has but B doesn't, the DeptNo B has but A doesn't? That means, can I use one magic select query to take care the equal join, left join and right join at one time?

Any suggestion is highly appreciated. |-0

 

[geopat]

Try FULL JOIN.

 

[jane30]

I'm Access97 user. Is there FULL JOIN for this version?

 

[geopat]

No, not in Access. However, if you just need DeptNo, then you can use UNION

Select DeptNo from TableA
Union
Select DeptNo from TableB

 

[MichaelRed]

Well, the Union query is the right direction, but it will populate the resultsset with the duplicates. There should be THREE sections:

[tab]TableA.DeptNo = TableB.DeptNo
Union
[tab]TableA.DeptNo <> TableB.DeptNo
Union
[tab]TableB.DeptNo <> TableA.DeptNo

Obviousl (?) incomplete, just to 'illustrate' that there are three sets of records in the Unique set between the Two tables.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

By default UNION queries eliminate duplicates. To include duplicates use UNION ALL. Or add another column to each query to distinguish the source table.

Select DeptNo, &quot;TableA&quot; As Tbl from TableA
Union
Select DeptNo, &quot;TableB&quot; As Tbl from TableB Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.