|
|
Posted by Branco Medeiros on 11/26/77 11:52
Hi all,
Using SQL Server 2000, SP4.
I have a table of street names (Rua) whose ids (cod_rua) are foreign
keys into a consumer table (Consumidor). It turns out that the "Rua"
table has many unused records which I'd like to wipe out. For instance,
there are some 2800 unused records in the "Rua" table, and only some
200 records actually being used by the "Consumidor" table (which,
itself, has some 5000 records).
Attempting to find the unused records, I issued the following query:
a)
SELECT COD_RUA FROM RUA
WHERE COD_RUA NOT IN (
SELECT COD_RUA FROM CONSUMIDOR
)
To my surprise, the query came out empty. But the following query
showed the 200 or so records which *are* being used:
b)
SELECT COD_RUA FROM RUA
WHERE COD_RUA IN (
SELECT COD_RUA FROM CONSUMIDOR
)
I've found two solutions for the query to list the records *not
existing* in the Consumidor table:
c)
SELECT COD_RUA FROM RUA
WHERE COD_RUA NOT IN (
SELECT COD_RUA FROM CONSUMIDOR
WHERE COD_RUA IS NOT NULL
)
d)
SELECT COD_RUA FROM RUA
WHERE COD_RUA NOT IN (
SELECT COD_RUA FROM RUA
WHERE COD_RUA IN (
SELECT COD_RUA FROM CONSUMIDOR
)
)
I know that there are many other possible solutions to the query
(including left joins), but what I don't understand is why the query a)
failed.
Can some of you, oh mighty gurus, enlighten me?
For the record, here's how both tables are (partially) declared:
CREATE TABLE Rua (
Cod_Rua int NOT NULL ,
Rua varchar (35) NULL ,
-- ...
-- other unrelated fields
-- ...
CONSTRAINT Pk_CodRua
PRIMARY KEY (Cod_Rua)
)
CREATE TABLE Consumidor (
Cod_Consumidor int NOT NULL ,
Cod_Rua int NULL ,
-- ...
-- other unrelated fields
-- ...
CONSTRAINT Pk_CodConsumidor
PRIMARY KEY(Cod_Consumidor) ,
CONSTRAINT Fk_CodRua_Consumidor
FOREIGN KEY (Cod_Rua)
REFERENCES Rua (Cod_Rua)
)
Regards,
Branco Medeiros
Navigation:
[Reply to this message]
|