RE: T-SQL Statement

Sounds like you need to use an outer join.

select ID, Firstname
from TableB b left outer join TableA a on a.ID = b.ID


"Alastair MacFarlane" wrote:

> Dear Group,
>
> Can someone please advise me of the way to achieve the following:
>
> I have 2 select statements that return an identical schema of columns. There
> may be some records in Table B with the same ID as in Table A. I would want
> all results from Select A and only the records from Select B that did not
> have an accompanying record in Select A. Table A is the main table.
>
> (A) SELECT ID, FirstName from TableA (RETURN ALL FROM A)
>
> ---UNION---
>
> (B) SELECT ID, FirstName from TableB (RETURN ONLY THOSE WITH NO RECORD IN A)
>
> I would like to select all From B that has not got a record in the ID column
> in select statement B and then UNION this resultset onto SELECT Select A. In
> Access you would use an Outer Join with the ID column from the Select A and
> UNION this onto the select Select A resultset.
>
> Can anyone please offer me some advice. Thanks again.
>
> Alastair MacFarlane
>

You wouldn't union it. Outer join's take all records from one table, and all
the records from the other table even if a record doesnt exist in the joined
table. you can look up Outer Joins in Books Online or you can look at this
article with examples

http://www.dotnetspider.com/resource...-Examples.aspx



"Alastair MacFarlane" wrote:

> John,
>
> Thanks for the reply. What I am not sure how to do, is to take this left
> outer join resultset and then UNION it onto the first Table all in one T-SQL
> statement.
>
> Thanks.
>
> Alastair
>
> "John Barr" wrote in message
> news:56A0B12B-FB25-4A5C-8CFF-64F35B3CD46D-at-microsoft.com...
> > Sounds like you need to use an outer join.
> >
> > select ID, Firstname
> > from TableB b left outer join TableA a on a.ID = b.ID
> >
> >
> > "Alastair MacFarlane" wrote:
> >
> >> Dear Group,
> >>
> >> Can someone please advise me of the way to achieve the following:
> >>
> >> I have 2 select statements that return an identical schema of columns.
> >> There
> >> may be some records in Table B with the same ID as in Table A. I would
> >> want
> >> all results from Select A and only the records from Select B that did not
> >> have an accompanying record in Select A. Table A is the main table.
> >>
> >> (A) SELECT ID, FirstName from TableA (RETURN ALL FROM A)
> >>
> >> ---UNION---
> >>
> >> (B) SELECT ID, FirstName from TableB (RETURN ONLY THOSE WITH NO RECORD IN
> >> A)
> >>
> >> I would like to select all From B that has not got a record in the ID
> >> column
> >> in select statement B and then UNION this resultset onto SELECT Select A.
> >> In
> >> Access you would use an Outer Join with the ID column from the Select A
> >> and
> >> UNION this onto the select Select A resultset.
> >>
> >> Can anyone please offer me some advice. Thanks again.
> >>
> >> Alastair MacFarlane
> >>
>