Better Query
-
--I have a table Skills which are grouped
CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));
INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');
Assume we have a table of job candidates:
CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));
INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');
--I need the names of Candidates who are having skills belonging to one group
--I have two soluntion can any one specify better way
SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);
GO
SELECT DISTINCT candidate_name FROM
(
SELECT C.candidate_name,COUNT(C.Skill)AS Counts1,Q.and_grp
FROM Candidates C INNER JOIN Query Q ON C.Skill=Q.Skill
GROUP BY C.candidate_name,Q.and_grp
) AS T1
INNER JOIN (SELECT and_grp,COUNT(*)AS Counts FROM Query
GROUP BY and_grp)
AS T ON T.and_grp=T1.and_grp
WHERE Counts1=Counts
--Thanx in Advance
-
ningaraju.n (12/16/2009)
--I need the names of Candidates who are having skills belonging to one groupCan you explain how Moe is a "winner"?
INSERT INTO #Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO #Candidates VALUES ('Moe', 'PHP');
INSERT INTO #Candidates VALUES ('Moe', 'Java');
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Moe is winne because he has the skills PHP and java which belongs to Group 2
-
ningaraju.n (12/17/2009)
Moe is winne because he has the skills PHP and java which belongs to Group 2Must a winner have all the skills of the group?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
he should have at least skills of one group...
-
I think from your description, you're after candidates with all skills from one or more skills groups. So in your example
the candidate would have to have both "Java and Perl" or "Java and PHP".
Here a query to do this which isn't greatly different from your first.
WITH SkillsCount AS (SELECT and_grp,
skill,
COUNT(*) OVER(PARTITION BY and_grp) AS grpCount
FROM Skills)
SELECT DISTINCT c.candidate_name
FROM SkillsCount s
INNER JOIN Candidates c ON c.skill = s.skill
GROUP BY s.and_grp, s.grpCount, c.candidate_name
HAVING COUNT(*)=s.grpCount;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Friend ,
Thanks for your Query,I tried and compare with the other possibilities that i have mentioned in post, but yours taking more reads than other possibilities.
Please check it once
-
ningaraju.n (12/18/2009)
Friend ,Thanks for your Query,I tried and compare with the other possibilities that i have mentioned in post, but yours taking more reads than other possibilities.
Please check it once
Can you define your requirements? It's still very unclear what you are looking for and why.
<<he should have at least skills of one group... >> this is obvious from your expected output in your earlier post. How many skills from how many groups?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
In my examples there are two groups
Group1: Java,Perl
Group2: Java,PHP
If an Candidate has both java and perl knowledge and then he has the full knowledge of Group1 then he is winner
If an Candidate has Perl and PHP knowledge and then he dont have full knowledge of either Group1 or Group2 then he is looser
Did i meet your expectation?
-
ningaraju.n (12/18/2009)
In my examples there are two groupsGroup1: Java,Perl
Group2: Java,PHP
If an Candidate has both java and perl knowledge and then he has the full knowledge of Group1 then he is winner
If an Candidate has Perl and PHP knowledge and then he dont have full knowledge of either Group1 or Group2 then he is looser
Did i meet your expectation?
Full knowledge of either group = winner?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ningaraju.n (12/18/2009)
In my examples there are two groupsGroup1: Java,Perl
Group2: Java,PHP
If an Candidate has both java and perl knowledge and then he has the full knowledge of Group1 then he is winner
If an Candidate has Perl and PHP knowledge and then he dont have full knowledge of either Group1 or Group2 then he is looser
Did i meet your expectation?
No, sorry, you have not.
INSERT INTO #Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO #Candidates VALUES ('Larry', 'PHP');
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
yes...you are right
-
ningaraju.n (12/18/2009)
yes...you are rightRestate your requirements - this is a very straightforward query.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ok sorry
-
Thank you friend
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply