Query using 'AND' on a linking table

Hi

I'm not an SQL expert and I'm having some problems doing a query on a linking table. I'm using SQL Server Compact Edition with the tables in question having the following (simplified) structure:

Contacts Table: ContactId (pk), DisplayName

SkillInstances Table: SkillInstID (pk), ObjectId(fk),SkillsId(fk)

Skills Table: SkillsId(pk), Skill

(The ObjectId in SkillInstances is ContactId in the Contacts table)

The idea is that a Contact can have one or more 'Skills'. Each Skill for a Contact has an SkillInstances row which references the particular Skill in the Skills table.

Typically I would want a query which returns Contacts with skills 'ABC' or 'XYZ'. This is no problem. But I cannot formulate a query that returns all Contacts with skills 'ABC' AND 'XYZ'.

SELECT Contacts.DisplayName
FROM SkillInstances INNER JOIN
Skills ON SkillInstances.SkillId = Skills.SkillId INNER JOIN
Contacts ON SkillInstances.ObjectId = Contacts.ContactId
WHERE (Skills.Skill = 'ABC') OR (Skills.Skill.= 'XYZ')

To get the AND condition, I tried creating two derived tables using the above SQL as a sub-queries but this does not seem to work on SQL Server CE.

Any ideas or thoughts much appreciated.

Regards

John Wilkie

Try the examples below which should both return the same results - one may be more performant than the other with your data.

Chris

Code Snippet

SELECT c.DisplayName

FROM Contacts c

WHERE EXISTS ( SELECT 1

FROM SkillInstances ski

INNER JOIN Skills sk ON sk.SkillID = ski.SkillID

WHERE ski.ObjectID = c.ContactID

AND sk.Skill = 'ABC' )

AND EXISTS ( SELECT 1

FROM SkillInstances ski

INNER JOIN Skills sk ON sk.SkillID = ski.SkillID

WHERE ski.ObjectID = c.ContactID

AND sk.Skill = 'XYZ' )

GO

SELECT c.DisplayName

FROM Contacts c

INNER JOIN SkillInstances ski ON ski.ObjectID = c.ContactID

INNER JOIN Skills sk ON sk.SkillID = ski.SkillID

WHERE sk.Skill IN('ABC', 'XYZ')

GROUP BY c.ContactID, c.DisplayName

HAVING COUNT(DISTINCT sk.Skill) = 2

GO

|||hi try this..

SELECT Contacts.DisplayName
FROM SkillInstances INNER JOIN
Skills ON SkillInstances.SkillId = Skills.SkillId INNER JOIN
Contacts ON SkillInstances.ObjectId = Contacts.ContactId
WHERE (Skills.Skill = 'ABC')
OR (Skills.Skill.= 'XYZ')
GROUP BY
Constacts.DisplayName
HAVING COUNT(Skills.Skill) = 2|||

here few more...

Code Snippet

Create Table #contacts(

[ContactId] int ,

[DisplayName] Varchar(100)

);

Insert Into #contactsValues('1','Nancy');

Insert Into #contactsValues('2','Andrew');

Insert Into #contactsValues('3','Janet');

Insert Into #contactsValues('4','Margaret');

Insert Into #contactsValues('5','Steven');

Create Table #skills (

[SkillsId] int ,

[Skill] Varchar(100)

);

Insert Into #skills Values('1','ASP.NET');

Insert Into #skills Values('2','SQL Server');

Insert Into #skills Values('3','C#');

Insert Into #skills Values('4','JavaScript');

Create Table #skillinstances (

[SkillInstID] int ,

[ObjectId] int ,

[SkillsId] int

);

Insert Into #skillinstances Values(1,1,1);

Insert Into #skillinstances Values(2,1,2);

Insert Into #skillinstances Values(3,1,3);

Insert Into #skillinstances Values(4,2,1);

Insert Into #skillinstances Values(5,2,2);

Insert Into #skillinstances Values(6,3,1);

Insert Into #skillinstances Values(7,3,3);

Insert Into #skillinstances Values(8,4,1);

Insert Into #skillinstances Values(9,4,2);

Insert Into #skillinstances Values(10,5,1);

Insert Into #skillinstances Values(11,5,3);

Insert Into #skillinstances Values(12,5,4);

--Using IN

SELECT

C.DisplayName

FROM

#SkillInstances SI

JOIN #Skills S1 ON SI.SkillsId = S1.SkillsIdAnd S1.Skill in ('ASP.NET')

INNER JOIN #Contacts C ON SI.ObjectId = C.ContactId

Where SI.ObjectId In

(

SELECT

SI.ObjectId

FROM

#SkillInstances SI

JOIN #Skills S1 ON SI.SkillsId = S1.SkillsIdAnd S1.Skill in ('C#')

)

--Using Exists

SELECT

C.DisplayName

FROM

#SkillInstances SIMain

JOIN #Skills S1 ON SIMain.SkillsId = S1.SkillsIdAnd S1.Skill in ('ASP.NET')

INNER JOIN #Contacts C ON SIMain.ObjectId = C.ContactId

Where Exists

(

SELECT

SI.ObjectId

FROM

#SkillInstances SI

JOIN #Skills S1 ON SI.SkillsId = S1.SkillsIdAnd S1.Skill in ('C#')

Where

SIMain.ObjectId = SI.ObjectId

)

--Simple & Faster One using Group By

SELECT

C.DisplayName

FROM

#SkillInstances SIMain

JOIN #Skills S1 ON SIMain.SkillsId = S1.SkillsIdAnd S1.Skill in ('ASP.NET','C#')

INNER JOIN #Contacts C ON SIMain.ObjectId = C.ContactId

Group By

C.DisplayName

Having

Count(Distinct Skill) =2

|||

Just to add that you should probably group by Contacts.ContactID (as in my example above) as you will receive eronous results if you group by only Contacts.DisplayName and your data contains duplicate Contacts.DisplayName values.

Chris

|||

Hi

Thank you for your quick response. Your solution works just fine. Thanks for your help.

Regards

John Wilkie

|||

Thanks to everyone for their prompt responses. All solutions seem to work fine. Thank you all again.

John Wilkie