Queries with NOT IN statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Queries with NOT IN statement

Author

Topic

martalex
Starting Member

17 Posts

Posted - 2005-01-20 : 20:55:59

I need to help with a couple of queries.

I have the following tables.

Contacts (cID, Group)
1, CADRE
2, CADRE
3, EXTRA
4, CADRE

Interactions_Contacts (cID, iID)
1, 5
1, 6
2, 5
2, 6
2, 7
2, 8
3, 6
3, 7
3, 8
4, 8

Interactions (iID, Initiative, Type)
5, CADRE, Electronics
6, CADRE, FACE-To-Face
7, EXTRA, FACE-To-Face
8, EXTRA, Electronic

I'm trying to execute the two following queries to find the contacts that meet the following critera.

Query 1:
Group EQUALS Cadre
AND
Initiative EQUALS Cadre
AND
Type NOT EQUALS Face To Face

SELECT DISTINCT contacts.Cid FROM
Contacts
LEFT JOIN Interactions_Contacts ON Contacts.Cid = Interactions_Contacts.Cid
LEFT JOIN Interactions ON Interactions_Contacts.Iid = Interactions.Iid
WHERE
Contacts.Group IN ('Cadre')
AND
interactions.Initiative IN ('Cadre')
AND
contacts.Cid NOT IN (SELECT Cid FROM interactions i INNER JOIN Interactions_Contacts ic on i.Iid = ic.Iid WHERE i.Type IN ('FACE-To-Face') AND i.Initiative IN ('Cadre'))

Is this the most efficient way to do this?

----

Query: 2
Group EQUALS Cadre
AND
Initiative NOT EQUALS Cadre
AND
Type NOT EQUALS Face To Face

I'm really not sure how to do this one bacause of the double NOT EQUALS.

Any help would be appreciated.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-20 : 23:03:09

Am I not reading this correctly???


DECLARE @Contacts TABLE (cID INT, [Group] VARCHAR(55))

INSERT @Contacts(cID, [Group])
	SELECT '1', 'CADRE' UNION ALL
	SELECT '2', 'CADRE' UNION ALL
	SELECT '3', 'EXTRA' UNION ALL
	SELECT '4', 'CADRE'

DECLARE @Interactions_Contacts TABLE(cID INT, iID INT)

INSERT @Interactions_Contacts(cID, iID)
	SELECT 1, 5 UNION ALL
	SELECT 1, 6 UNION ALL
	SELECT 2, 5 UNION ALL
	SELECT 2, 6 UNION ALL
	SELECT 2, 7 UNION ALL
	SELECT 2, 8 UNION ALL
	SELECT 3, 6 UNION ALL
	SELECT 3, 7 UNION ALL
	SELECT 3, 8 UNION ALL
	SELECT 4, 8

DECLARE @Interactions TABLE(iID INT, Initiative VARCHAR(55), Type VARCHAR(55))

INSERT @Interactions(iID, Initiative, Type)
	SELECT 5, 'CADRE', 'Electronics' UNION ALL
	SELECT 6, 'CADRE', 'FACE-To-Face' UNION ALL
	SELECT 7, 'EXTRA', 'FACE-To-Face' UNION ALL
	SELECT 8, 'EXTRA', 'Electronic'

-- Query: 2
-- Group EQUALS Cadre
-- AND
-- Initiative NOT EQUALS Cadre
-- AND
-- Type NOT EQUALS Face To Face

SELECT
	c.cID,
	c.[Group],
	i.iID, 
	i.Initiative,
	i.Type
FROM
	@Contacts c
	INNER JOIN @Interactions_Contacts ic ON c.cID = ic.cID
	INNER JOIN @Interactions i ON ic.iID = i.iID
WHERE
	c.[Group] = 'CADRE' --Group EQUALS Cadre
	AND i.Initiative <> 'CADRE' -- Initiative NOT EQUALS Cadre
	AND i.Type <> 'FACE-To-Face' -- Type NOT EQUALS Face To Face

-- Query 1:
-- Group EQUALS Cadre
-- AND
-- Initiative EQUALS Cadre
-- AND
-- Type NOT EQUALS Face To Face

SELECT
	c.cID,
	c.[Group],
	i.iID, 
	i.Initiative,
	i.Type
FROM
	@Contacts c
	INNER JOIN @Interactions_Contacts ic ON c.cID = ic.cID
	INNER JOIN @Interactions i ON ic.iID = i.iID
WHERE
	c.[Group] = 'CADRE' --Group EQUALS Cadre
	AND i.Initiative = 'CADRE' -- Initiative EQUALS Cadre
	AND i.Type <> 'FACE-To-Face' -- Type NOT EQUALS Face To Face

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

martalex
Starting Member

17 Posts

Posted - 2005-01-21 : 06:35:05

That is what I initially did but for Query 1 would that not return potentially contacts that may have interactions where the initiative was Cadre and Type not Face To Face.

My problem is with the many to many relationship. My understanding of the query is that I need to return all contacts where their group is Cadre and that at least one Interaction with Cadre as Initiative. Also, the Interaction for the contact where Initiative is Cadre cannot had Face-To-Face as Type. However, if the contact as Interaction where the Type is Face-To-Face but the initiative is not Cadre it's ok.

epanther
Starting Member

8 Posts

Posted - 2005-01-22 : 01:42:05

Maybe I'm reading this wrong, but it looks like all the data you are trying to get and evaluate is in the Interactions table. If that is true, I don't understand why you are trying to tie the three tables together. Am I missing something?

Panther

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-22 : 09:28:45

Martalex, I'm not sure if these are the results you're looking for but it may give you some ideas on a different approach. If you post what the results should be with your 2 sets of criteria we'd have a good chance of giving you what you need.

set nocount on
DECLARE @Contacts TABLE (cID INT, [Group] VARCHAR(55))

INSERT @Contacts(cID, [Group])
SELECT '1', 'CADRE' UNION ALL
SELECT '2', 'CADRE' UNION ALL
SELECT '3', 'EXTRA' UNION ALL
SELECT '4', 'CADRE'

DECLARE @Interactions_Contacts TABLE(cID INT, iID INT)

INSERT @Interactions_Contacts(cID, iID)
SELECT 1, 5 UNION ALL
SELECT 1, 6 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 2, 7 UNION ALL
SELECT 2, 8 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 3, 7 UNION ALL
SELECT 3, 8 UNION ALL
SELECT 4, 8

DECLARE @Interactions TABLE(iID INT, Initiative VARCHAR(55), Type VARCHAR(55))

INSERT @Interactions(iID, Initiative, Type)
SELECT 5, 'CADRE', 'Electronics' UNION ALL
SELECT 6, 'CADRE', 'FACE-To-Face' UNION ALL
SELECT 7, 'EXTRA', 'FACE-To-Face' UNION ALL
SELECT 8, 'EXTRA', 'Electronic'
/*
Query 1:
Group EQUALS Cadre
AND
Initiative EQUALS Cadre
AND
Type NOT EQUALS Face To Face
*/
Select c.*, i.*
From @interactions_contacts ic
JOIN @contacts c
ON ic.cid = c.cid
JOIN @interactions i
ON ic.iid = i.iid
Left JOIN @interactions ix
ON ic.iid = ix.iid
and ix.type = 'FACE-To-Face'
Where c.[group] = 'CADRE'
AND i.initiative = 'CADRE'
AND ix.iid is NULL

/*
Query: 2
Group EQUALS Cadre
AND
Initiative NOT EQUALS Cadre
AND
Type NOT EQUALS Face To Face
*/
Select c.*, i.*
From @interactions_contacts ic
JOIN @contacts c
ON ic.cid = c.cid
JOIN @interactions i
ON ic.iid = i.iid
Left JOIN @interactions ix
ON ic.iid = ix.iid
and ix.type = 'FACE-To-Face'
and ix.initiative = 'CADRE'
Where c.[group] = 'CADRE'
AND ix.iid is NULL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-22 : 09:37:22

Sorry for the lack of formatting in my previous post. I seem to be having some cut and paste issues :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources