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

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 04/30/04 

Date: Thu, 29 Apr 2004 19:22:11 -0700

1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. What you did post was actually vry good for a newbie!

2) Never use IDENTITY for a key. You can look up my rants about that.
You needed a key for Application_Servers and had the brains not to use
IDENTITY which is a typical newbie error! 10 points!

3) Rows are not records; fields are not columns; tables are not files;
there is no sequential access or ordering in an RDBMS, so "first",
"next" and "last" are totally meaningless. It really helps to have the
right mental model.

4) We don't use loops in SQL; this is a set-oriented language. We are
back to that mental model thing again.

5) That nvarchar(50) tells me that you did absolutely no design on this
schema and that you deserve to have all the garbage that is going to
accumlate in those columns. I'll be happy to add am emtire sutra in
Chinese myself!

6) Remember to add DRI actions; that is one of many reasons that rows
are not records; fields are not columns; tables are not files.

CREATE TABLE Applications
(app_id INTEGER NOT NULL PRIMARY KEY,
 app_name CHAR(30) NOT NULL);

CREATE TABLE Servers
(svr_id INTEGER NOT NULL PRIMARY KEY,
 svr_name CHAR(30) NOT NULL);

CREATE TABLE Application_Servers,
(app_id INTEGER NOT NULL
           REFERENCES Applications (app_id)
           ON DELETE CASCADE
           ON UPDATE CASCADE,
 svr_id INTEGER NOT NULL
           REFERENCES TABLE Servers (svr_id)
           ON DELETE CASCADE
           ON UPDATE CASCADE,
 PRIMARY KEY (app_id, svr_id));

>> I want to Return All of the Servers not Assigned to a given
application. <<

SELECT @my_app_id,
       svr_id AS missing_id, svr_name AS missing_name
  FROM Servers
 WHERE svr_id
       NOT IN (SELECT svr_id
                 FROM Application_Servers
                WHERE app_id = @my_app_id);

--CELKO--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Please help me with this query
    ... Please post DDL, so that people do not have to guess what the keys, ... schema are. ... please fire the idiot who gave the same data element so many ... (cust_id INTEGER NOT NULL PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)
  • Re: union replacing values
    ... Please post DDL, so that people do not have to guess what the keys, ... never use an EAV schema. ... (user_id INTEGER NOT NULL PRIMARY KEY, ... all organizations have a preference, all users have have a preference, ...
    (microsoft.public.sqlserver.programming)
  • RE: How is System.Data.DataTable.Load() determining whether a column is a primary key?
    ... DataTable.Loadbehavior through Reflected codes or debugging .NET sources ... To know how to debug into .NET Framework source codes, ... GetSchemaTableFromDataTable method to retrieve the schema. ... If the table has a primary key, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Two tables and count
    ... Please post DDL, so that people do not have to guess what the keys, ... constraints, Declarative Referential Integrity, datatypes, etc. in your ... approach and schema are an attempt to imitate a punch or paper form in ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Query. Is this possible?
    ... Please post DDL, so that people do not have to guess what the keys, ... constraints, Declarative Referential Integrity, data types, etc. in ... problem with trying to do this in an improperly designed schema. ... WITH ExpandedMembership ...
    (microsoft.public.sqlserver.programming)
Loading