Re: Newbie: Help Excluding Records from a Table using multiple rows from another table

From: Alan Howard (Xalan.howardX_at_Xparadise.net.nzX)
Date: 04/30/04 

Date: Fri, 30 Apr 2004 12:59:17 +1200

Try

select * from servers where svr_id NOT IN (select svr_id from
application_servers where APP_ID = @APP_ID)

Alan

"Jon" <jon@feutz.com> wrote in message
news:2dfc4578.0404291645.f6bf0f7@posting.google.com...
> I'm New to the SQL Game, and could use some help Excluding some
> records. I have the following Tables.
>
> Table:[applications]
> (Purpose: Holds Application Info
> App_ID int (Primary Key)(Identity)
> App_Name nvarchar(50)
> Example Data
> [APP_ID] [App_Name]
> 12 .NET Server Farm
> 13 SQL Servers
>
> Table:application_servers,
> (Purpose: Maps Svrs to Applications)
> Columns
> App_ID int (ForeignKey)
> Svr_ID int (Foreign key)
> [APP_ID] [Svr_ID]
> 12 200
> 12 201
> 12 202
> 13 331
> 13 332
>
> Table: servers
> (Purpose: Holds server info)
> svr_id (Primary Key)(Identity)
> svr_name
> [Svr_ID] [svr_name]
> 200 w2kweb1
> 201 w2kweb2
> 202 w2kweb3
> 331 w2ksql1
> 332 w2ksql2
> 334 w2kmail1
>
> The Problem is this. I want to Return All of the Servers not Assigned
> to a given application. So if the App_ID is 12, I want the query to
> return all servers not assigned to 12, which would be (331,332,334)
>
> I tried this:
> select * from servers As Svr join application_servers As App
> on Svr.Svr_ID = App.Svr_ID where App_ID <> @App_ID
> (which only returns servers that exist in both tables, I want it to
> return all servers except those assigned to a certain App_ID(12)
>
> I also tried this
> select * from servers where svr_id <> (select svr_id from
> application_servers where APP_ID = @APP_ID)
>
> This Also was an utter failure, because the application_server table
> of course returns multiple rows, and I quickly learned you can't do
> that with =,<>, etc.
>
>
> So from what I'm gathering, I think I need to do some sort of While
> Loop to handle the multiple rows, but the examples I've seen on
> google, I'm not sure how to setup the While Loop.

Relevant Pages