Query Question

[Eric Borden]

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]

Re: Query Question

Eric Borden (borden_eric@in valid.com) writes:[color=blue]
> 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)[/color]

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.)
[color=blue]
> 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[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Eric Borden]

Re: Query Question

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@sommars kog.se> wrote in message
news:Xns96A7B6B 0EC28Yazorman@1 27.0.0.1...[color=blue]
> Eric Borden (borden_eric@in valid.com) writes:[color=green]
>> 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)[/color]
>
> 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.)
>[color=green]
>> 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[/color]
>
> 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@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: Query Question

Eric Borden (borden_eric@in valid.com) writes:[color=blue]
> 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 ."[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: Query Question

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_statu s IN ('A', 'I', 'X', ...))
etc.
);

Now this is a simpel VIEW problem.

[Eric Borden]

Re: Query Question

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

"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns96A75D4 8F3AC4Yazorman@ 127.0.0.1...[color=blue]
> Eric Borden (borden_eric@in valid.com) writes:[color=green]
>> 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 ."[/color]
>
> 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@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]