December 27, 2005 at 11:20 pm
Hi,
I have following tables:
1. Problem_type (promblem_id, desc) -
values (1, some problem-1)
values (2, some problem-2)
2. Problem_skills (problem_id, skill)
values (1, skill-1)
values (1, skill-2)
values (1, skill-3)
values (2, skill-1)
values (2, skill-4)
values (2, skill-5)
3. Employee (employee_id, skill)
value (1, skill-1)
value( 1, skill-2)
value(1, skill-3)
values(1, skill-4)
values (2, skill-5)
values (2, skill-4)
values (2, skill-3)
values (3, skill-1)
values (3, skill-1)
I need help in determining which employee has the required skills matching to Problem -1. Based on sample data I prepared, for Problem 1, skills required are skill-1, skill-2, skill-3. The employee with these skills is Employee 1.
Please help me how I can do multi-row comparision.
thanks in advance
Rajesh
December 28, 2005 at 1:39 am
Hi,
Try the following using a derived table to get a skill count for the problem in question and matching it against the employee with the required number of skills per problem.
Select a.Problem_ID, A.Descr, A.ReqSkillCnt, c.Employee_id, Count(c.employee_ID) as AvailSkill
From (select a.problem_id as Problem_ID, a.descr as Descr, count(*) as ReqSkillCnt
from problem_type a, problem_skills b
where a.problem_id = b.problem_id
and a.descr = 'some problem-1'
group by a.problem_id, a.descr) as a,
problem_skills b,
employee c
where a.Problem_id = b.problem_id
and b.skill = c.skill
group by a.problem_id, a.descr, a.reqskillcnt, c.employee_id
having count(c.employee_id) = a.reqskillcnt
Hope it helps
The Aethyr Dragon
Cape Town
RSA
December 28, 2005 at 2:38 am
Hi,
Thanks, I tried it and it worked as desired
thanks for the quick help
Rajesh
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply