Monday, March 26, 2012

many-to-many selection

Hi all, I am stuck on the case of many-to-many selection.

Now, I have 3 tables

Table: candidate_skills
candidate_id (PKey)
skill_id (PKey)

Table: skills
skill_id (PKey)

Table: job_skill_required
job_id(PKey)
skill_id(PKey)

Now, I want to select all cadidates who has the same skills of job_skill_required table.

I really don't know how to select it...
Please help, thank you.what's the output supposed to look like? see the sticky at the top of this page.|||you're up early, sean :)
select candidate_id
from candidate_skills
where skill_id
in ( select skill_id
from job_skill_required
where job_id = 937 )
group
by candidate_id
having count(*)
= ( select count(*)
from job_skill_required
where job_id = 937 )|||At the office by 7:15 everyday because little of substance gets done between 10 and 5.

Playing around with this Embracadero Change Manager thingy because one of my clients db's is woefully out of sync. Not sure if it is going to do what I need it to do yet.|||create table #candidate_skills
(
candidate_id int,
skill_id int
)
go
create table #job_skill_required
(
job_id int,
skill_id int
)
go
insert into #candidate_skills select 1,1
union
select 1,2
union
select 1,4
union
select 2,1
union
select 2,2
union
select 2,3
union
select 3,1
union
select 3,2
union
select 3,3
union
select 3,6
union
select 4,7
union
select 4,1
union
select 4,2

insert into #job_skill_required select 1,1
union
select 1,2
union
select 1,3
union
select 2,1
union
select 2,2
union
select 2,4
union
select 3,7
union
select 4,8

--select * from #job_skill_required j
--select c.* from #candidate_skills c,

select c.candidate_id,j.job_id from #candidate_skills c,#job_skill_required j
where j.skill_id=c.skill_id
group by c.candidate_id,j.job_id
having count(*)=(select count(distinct(skill_id)) from #job_skill_required j1 where j1.job_id=j.job_id)
order by c.candidate_id|||Thanks for your fast-reply...

No comments:

Post a Comment