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

From: Jon (jon_at_feutz.com)
Date: 04/30/04 

Date: 29 Apr 2004 17:45:34 -0700

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