Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what's the output supposed to look like? see the sticky at the top of this page.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're up early, sean
    Code:
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Code:
    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
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    Apr 2004
    Posts
    8

    Thank so much

    Thanks for your fast-reply...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •