Microsoft SQL Server forum

Author

Query Question

Eric Borden

2005-08-02, 8:24 pm

I have two tables.
The first one is called Protocols and contains the following columns:
ID (table key)
Name

The second is called ActiveProtocols and contains the following columns:
ID (table key)
ProtocolsID (foreign key to Protocols)

I want to create a dataset that will exclude all rows from Protocols that
are found in ActiveProtocols.ProtocolsID.

I have tried various ways to do this but haven't been successful.
The closest I have come to a solution is the following Theta Join:

SELECT Protocols.ID FROM Protocols
JOIN ActiveProtocols ON ActiveProtocols.ProtocolID <> Protocols.ID

This almost works. I initially get the excluded rows but then I also get
every row from Protocols too.

Thanks in advance,
Eric

Erland Sommarskog

2005-08-02, 8:24 pm

Eric Borden (borden_eric@invalid
.com) writes:
> I have two tables.
> The first one is called Protocols and contains the following columns:
> ID (table key)
> Name
>
> The second is called ActiveProtocols and contains the following columns:
> ID (table key)
> ProtocolsID (foreign key to Protocols)

Can a protocol be active more than once? Or else, why do you have an
extra ID column? Should not ProtocolsID be sufficient for a table?

(And, in many cases, it may be simple to just have an is_active flag
in the base table.)

> I want to create a dataset that will exclude all rows from Protocols that
> are found in ActiveProtocols.ProtocolsID.
>
> I have tried various ways to do this but haven't been successful.
> The closest I have come to a solution is the following Theta Join:
>
> SELECT Protocols.ID FROM Protocols
> JOIN ActiveProtocols ON ActiveProtocols.ProtocolID <> Protocols.ID

Using <> for a join operation is a very rare thing to do. This is
almost the same as a cartesian product.

This is what you need:

SELECT P.ID
FROM Protocols P
WHERE NOT EXISTS (SELECT *
FROM ActiveProtocols AP
WHERE AP.ProtocolsID = P.ID)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Eric Borden

2005-08-03, 3:23 am

Thanks for your input on the query statement. It works great!

As for your question about a protocol being active more than once
the answer is yes. I didn't include all the columns of ActiveProtocols
for simplicity sake. I created a seperate key for ActiveProtocols
because I have read that it is better practice to have a single key
than multiple keys to define "uniqueness."
I am definately not an expert design of databases, so if you think
there is a better way, I'm all ears!

Thanks again,
Eric

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96A7B6B0EC28
Yazorman@127.0.0.1...
> Eric Borden (borden_eric@invalid
.com) writes:
>
> Can a protocol be active more than once? Or else, why do you have an
> extra ID column? Should not ProtocolsID be sufficient for a table?
>
> (And, in many cases, it may be simple to just have an is_active flag
> in the base table.)
>
>
> Using <> for a join operation is a very rare thing to do. This is
> almost the same as a cartesian product.
>
> This is what you need:
>
> SELECT P.ID
> FROM Protocols P
> WHERE NOT EXISTS (SELECT *
> FROM ActiveProtocols AP
> WHERE AP.ProtocolsID = P.ID)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Erland Sommarskog

2005-08-03, 3:23 am

Eric Borden (borden_eric@invalid
.com) writes:
> As for your question about a protocol being active more than once
> the answer is yes. I didn't include all the columns of ActiveProtocols
> for simplicity sake. I created a seperate key for ActiveProtocols
> because I have read that it is better practice to have a single key
> than multiple keys to define "uniqueness."

I don't where you read that, but I don't agree. Admittedly, a one-column
key can be somewhat easier to manage client-side, but since you
introduce redudancy, it's good to learn master multi-column keys.

It's always a good thing to look for a natural key, that is a key that
can be found in the data. This is far from always trivial, because the
world offers a lots of ifs and buts. But if know that there is a
business rules that says that a protocol and be active at once given
a ContextID, then the key of ActiveProtocols should be (ProtocolID,
ContextID). And if you still hav an artificial key, such ID, you
should have a UNIQUE constraint on the real key.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

--CELKO--

2005-08-03, 11:24 am

It sounds like being active is a status, not an entity. This si
called attribute splitting, where you turn an attribvute into a new
table.

CREATE TABLE Protocols
(protocol_nbr INTEGER NOT NULL PRIMARY KEY,
protocol_name CHAR(20) NOT NULL,
protocol_status CHAR(1) DEFAULT 'A' NOT NULL
CHECK (protocol_status IN ('A', 'I', 'X', ...))
etc.
);

Now this is a simpel VIEW problem.

Eric Borden

2005-08-03, 8:24 pm

Thanks again for your input, I will use your idea.
I appreciate the time you took to help me learn.
Eric

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns96A75D48F3AC
4Yazorman@127.0.0.1...
> Eric Borden (borden_eric@invalid
.com) writes:
>
> I don't where you read that, but I don't agree. Admittedly, a one-column
> key can be somewhat easier to manage client-side, but since you
> introduce redudancy, it's good to learn master multi-column keys.
>
> It's always a good thing to look for a natural key, that is a key that
> can be found in the data. This is far from always trivial, because the
> world offers a lots of ifs and buts. But if know that there is a
> business rules that says that a protocol and be active at once given
> a ContextID, then the key of ActiveProtocols should be (ProtocolID,
> ContextID). And if you still hav an artificial key, such ID, you
> should have a UNIQUE constraint on the real key.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Sponsored Links