|
|
Posted by Erland Sommarskog on 08/03/05 02:09
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/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|