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
or is there an option/tool to help me out w/ this.