Help with Query

I have to tables
Table A Columns:
IDNumber
Text
Existing

Table B Columns:
TableAForeignKeyID
TableBID
OtherInfo
Existing

I want to select all rows from Table A where Existing = 1.
I also want to select all rows from Table A where TableB refers to it and TableB.Existing = 1 (regardless of TableA.Existing).

Separately the queries are like:

Code Snippet

Select * from TableA where Existing = 1;

Select IDNumber,Text,TableA.Existing from TableA, TableB
where TableA.IDNumber = TableB.TableAForeignKeyID
and TableB.Existing = 1;

How can I combine the two (or write a different query) to get both in a single result set?

Use the following query...

Code Snippet

Select

IDNumber

,Text

,TableA.Existing

from

TableA

FULL OUTER JOIN TableB

ON TableA.IDNumber = TableB.TableAForeignKeyID

and TableB.Existing = 1

and TableA.Existing = 1

|||

Code Snippet

Select * from TableA where Existing = 1
UNION ALL --or >> UNION if you don't want duplication
Select IDNumber,Text,TableA.Existing from TableA, TableB
where TableA.IDNumber = TableB.TableAForeignKeyID
and TableB.Existing = 1

you could also use a left outer join
coding would vary on whether you want complete sets or an intersection|||Here's another query that would add a column that identifies whether there is additional information in TableB about any row item of TableA.

Code Snippet

Select *, '0' as ExtraInfo from TableA TA where Existing = 1 and not exists
(Select * from TableB TB, TableA where TA.IDNumber = TB.TableAForeignKeyID)
UNION
Select IDNumber,Text,TableA.Existing, '1' as ExtraInfo from TableA, TableB
where TableA.IDNumber = TableB.TableAForeignKeyID
and TableB.Existing = 1

DaleJ wrote:

Code Snippet

Select * from TableA where Existing = 1
UNION ALL --or >> UNION if you don't want duplication
Select IDNumber,Text,TableA.Existing from TableA, TableB
where TableA.IDNumber = TableB.TableAForeignKeyID
and TableB.Existing = 1

you could also use a left outer join
coding would vary on whether you want complete sets or an intersection