Outer Join?

I am having troubles with a query that may have to use an Outer Join.
I would appreciate any help with this problem.
I will pass two parameters to the stored procedure.
@.OldStnType and @.NewStnType.
I want to see which records exist in @.OldStnType and not in
@.NewStnType. In this example:
set @.OldStnType='BUILD'
set @.NewStnType='SCHED'
Since only optionnbr 4 exists in Build and Not in Sched, I want to
return the record the following:
StnType OptionNbr
========== =============
BUILD 4
CREATE TABLE [dbo].[cfgStnOptionList] (
[StnType] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OptionNbr] [int] NOT NULL ,
[Description] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdated] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert into cfgStnOptionList values ('BUILD',3,'Station 1310
Option','Jan 2, 2005')
Insert into cfgStnOptionList values ('BUILD',4,'Test 4','Jan 1, 2005')
Insert into cfgStnOptionList values ('SCHED',1,'Marquee CommPort','Jan
1, 2005')
Insert into cfgStnOptionList values ('SCHED',2,'Scanner CommPort','Jan
1, 2005')
Insert into cfgStnOptionList values ('SCHED',3,'Station 12313','Jan 1,
2005')
Thank You,
Dave.Hi
These are untested, but try:
SELECT A.[StnType] , A.[OptionNbr]
FROM [dbo].[cfgStnOptionList] A
LEFT JOIN [dbo].[cfgStnOptionList] B ON A.[OptionNbr] = B.[OptionNbr] AND
B.[StnType] = @.NewStnType
WHERE B.[OptionNbr] IS NULL
AND A.[StnType] = @.OldStnType
Or
SELECT A.[StnType] , A.[OptionNbr]
FROM [dbo].[cfgStnOptionList] A
WHERE NOT EXISTS ( SELECT * FROM [dbo].[cfgStnOptionList] B WHERE
A.[OptionNbr] = B.[OptionNbr] AND B.[StnType] = @.NewStnType )
AND A.[StnType] = @.OldStnType
John
"Dave" wrote:

> I am having troubles with a query that may have to use an Outer Join.
> I would appreciate any help with this problem.
> I will pass two parameters to the stored procedure.
> @.OldStnType and @.NewStnType.
> I want to see which records exist in @.OldStnType and not in
> @.NewStnType. In this example:
> set @.OldStnType='BUILD'
> set @.NewStnType='SCHED'
> Since only optionnbr 4 exists in Build and Not in Sched, I want to
> return the record the following:
>
> StnType OptionNbr
> ========== =============
> BUILD 4
>
>
> CREATE TABLE [dbo].[cfgStnOptionList] (
> [StnType] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OptionNbr] [int] NOT NULL ,
> [Description] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastUpdated] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> Insert into cfgStnOptionList values ('BUILD',3,'Station 1310
> Option','Jan 2, 2005')
> Insert into cfgStnOptionList values ('BUILD',4,'Test 4','Jan 1, 2005')
> Insert into cfgStnOptionList values ('SCHED',1,'Marquee CommPort','Jan
> 1, 2005')
> Insert into cfgStnOptionList values ('SCHED',2,'Scanner CommPort','Jan
> 1, 2005')
> Insert into cfgStnOptionList values ('SCHED',3,'Station 12313','Jan 1,
> 2005')
> Thank You,
> Dave.
>|||That worked great John!
Thank you for your help.
Dave.
On Tue, 10 May 2005 04:36:02 -0700, John Bell
<jbellnewsposts@.h0tmail.com> wrote:
>Hi
>These are untested, but try:
>SELECT A.[StnType] , A.[OptionNbr]
>FROM [dbo].[cfgStnOptionList] A
>LEFT JOIN [dbo].[cfgStnOptionList] B ON A.[OptionNbr] = B.[OptionNbr] AND
>B.[StnType] = @.NewStnType
>WHERE B.[OptionNbr] IS NULL
>AND A.[StnType] = @.OldStnType
>Or
>SELECT A.[StnType] , A.[OptionNbr]
>FROM [dbo].[cfgStnOptionList] A
>WHERE NOT EXISTS ( SELECT * FROM [dbo].[cfgStnOptionList] B WHERE
>A.[OptionNbr] = B.[OptionNbr] AND B.[StnType] = @.NewStnType )
>AND A.[StnType] = @.OldStnType
>John
>"Dave" wrote:
>sql