Problem doind a query - Might be a bad join

[McKaulick]

Problem doind a query:

Here's the deal, I have table call ContactSkill with 2 fields

1)Contact_key
2)Skill_Key

And another one call Search_Skill with 2 fields

1) Skill_Key
2) Search_Key

I want to retrive all contact_key that have all the skills selected from a given search_key.

Right know, it returns all contact_key but not of them should be return.

Here's my query (Actually it should not return any contact because I check 6 skills and it return contact even if it only have one skill of one of the 6 skills that have been checked.

SELECT co_vi.FK_CONTACT_KEY AS conkey FROM contact_skills AS co_ski INNER JOIN
(search_skills AS se_vi) ON (se_vi.FK_SKILLS_UID = co_vi.FK_SKILLS_UID AND se_vi.FK_SEARCH_SN = 'co8t1usouxwapqc7oc8o')

Thanks for any help!

 

[r937]

select co_vi.fk_contact_key as conkey 
  from search_skills as se_vi 
inner 
  join contact_skills as co_ski
    on co_vi.fk_skills_uid = se_vi.fk_skills_uid
 where se_vi.fk_search_sn = 'co8t1usouxwapqc7oc8o'
group
    by conkey
having count(*)
     = ( select count(*)
           from search_skills 
          where fk_search_sn = 'co8t1usouxwapqc7oc8o' )

r937.com | rudy.ca

 

[McKaulick]

Thanks you so much r937 it works great!

You are the man!