# question

## remove some rows based on primary key values

we have a table have primary key PID and foreign key CID, now need to clean some rows based on primary key. the table like below: CREATE TABLE #AA ( PID INT , CID INT , PC VARCHAR(10) ) INSERT INTO #AA ( PID, CID, PC ) VALUES ( 1, NULL, 'P' ), ( 3, NULL, 'P' ), ( 4, 1, 'A' ), ( 5, 1, 'A' ), ( 6, 3, 'A' ), ( 7, 2, 'A' ), ( 8, 2, 'A' ), ( 9, 2, 'A' ) the output need to remove the CID not in PID. OUTPUT like: 1,NULL,'P' 3,NULL,'P' 4,1,'A' 5,1,'A' 6,3,'A' Thanks

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
What have you tried so far and the results?
0 Likes 0 ·
·
do you have solution for it?
0 Likes 0 ·

·
SELECT * FROM #AA EXCEPT SELECT * FROM #AA WHERE CID not IN (SELECT PID FROM #AA) this works but not a ideal way of doing it

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The script in the original answer already works, but here is another version: select a.* from #AA a left join #AA b on a.CID = b.PID where a.CID is null or b.PID is not null; The idea is that the rows with CID is null need to be included even though they don't have a counterpart. The rows with not null values in the CID need to be valid (from the existing PID values). The script produces the following output for the given set of rows in question: PID CID PC ----------- ----------- ---------- 1 NULL P 3 NULL P 4 1 A 5 1 A 6 3 A I am not sure whether this script is cheaper or not, but should be easy enough to test. Hope this helps, Oleg

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.