Is this the same query? Or am I crazy?

Okay, so a third party gave me the following SQL snippet.

delete from isiContacts
where DescriptionID not in (2,6,5,4)
and ContactID not in (
	select ContactID from isiContacts
	where NameID  in(
				select NameID from isiContacts
				where DescriptionID in (2,6,5,4)
			)
 )

Correct me if I’m wrong, but couldn’t this be simplified using

delete from isiContacts
where DescriptionID not in (2,6,5,4)

Do I need my caffeine before pursuing this, this morning?

its not the same query, you need caffeine

the query is filtering on 2 different ID’s ( DescriptionID and ContactID)

your new query is only filtering on 1 (DescriptionID)

It depends on the contents of the table.

If the third party isn’t completely crazy, it looks like nameid’s can have multiple contactid’s, and if only one of those nameid/contactid pairs has descriptionid in (2, 6, 5, 4) then you shouldn’t delete any of those contactids (without considering the nameid again, so maybe it’s an n-n relationship?)

+1, thank you! I knew I had to be missing something. :slight_smile:

No, it’s not the same, and yes, you’re crazy :smiley:

The query you want to run is deleting all contacts that aren’t of a specific description.

The original query deletes all contacts that aren’t of a specific description isn’t in a range AND doesn’t tie to anyone that is related to any of the descriptions in the range.

So, for example, if you had these rows on your table:

Joe (nameID = 10) is in your contact list for golf (2), frisbee(6), kayaaking(5), hiking(4) and extreme cupcake making.
Ralph (nameID = 20) is in your contact list for kickball, tetherball, soccer golf, and extreme cupcake making.
HAWK (nameID = 30) is in your contact list for kickboxing, frisbee(6), soccer golf, hiking(4) and extreme cupcake making.

Your query would delete all rows for Ralph, all but two rows for HAWK and all rows for Joe except for extreme cupcake making
The sample query would delete all rows for Ralph, and none for Joe or HAWK because Joe had four of the searched for types, and none for HAWK because she had frisbee and hiking.

Or to look at it from a data perspective


Joe = NameID 10, Ralph = NameID 20, HAWK = NameID 30									


ContactID	DescriptionID	  NameID	
1			2				10
2			4				10
3			5				10
4			6				10
5			7				10
6			8				10
7			9				20
8			10				20
9			11				20
10			12				20
11			13				30
12			4				30
13			14				30
14			6				30
15			15				30


After your query
ContactID	DescriptionID	   NameID	
5			7				10
6			8				10
12			4				30
14			6				30


After the original query:
ContactID	DescriptionID	    NameID	
1			2				10
2			4				10
3			5				10
4			6				10
5			7				10
6			8				10
11			13				30
12			4				30
13			14				30
14			6				30
15			15				30

+1 for charting it out!