[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

SQL SERVER QUERY

TABLES
Hi, I have the above tables.

Candidats have one or more qualifications.
Profils requires two or more qualifications.

1- Given a profil, I need to select all candidats who have all the required qualifications.

2- I need to able to select the required qualifications from a profil and get the list of candidats. For instance, given a profil requiring 5 qualifications, I need to be able to select 3 required qualifications and get the list of candidats who have these qualifications.

Thanks experts.
0
bkourouma
Asked:
bkourouma
  • 4
  • 3
  • 2
1 Solution
 
Lara FEACommented:
--general idea. Given you need to have candidates who have those 3 qualifications
declare @q1 int, @q2 int, @q3 int
select @q1 =1, @q2 =2, @q3=3

-- this query will give list of candidated who have all 3 qualifications
select c.candidat_id, count(distinct q.qualification_id)
from candidats c join cadidats_qualifications q
      on c.candidat_id=q.candidat_id
where q.qualification_id in (@q1, @q2, @q3)
group by c.candidat_id
having count(distinct q.qualification_id)=3
0
 
Lara FEACommented:
-- or using profile ID
declare @profileID int, @qCount int
select @profileID =1
select @qCount = count(qualification_id) from profil_qualifications

select c.candidat_id, count(distinct q.qualification_id)
from candidats c join cadidats_qualifications q
      on c.candidat_id=q.candidat_id
where q.qualification_id in (select qualificationid from profil_qualifications where profile_id =@profileID )
group by c.candidat_id
having count(distinct q.qualification_id)=@qCount
0
 
bkouroumaAuthor Commented:
Hi there, thanks for your input, however on the first case, i need candidates who have AT LEAST those 3 qualifications. I supposed that in the code you gave, I will only get candidats who have EXACTLY those 3 qualifications.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ralmadaCommented:
try

select c.candidat_id, count(distinct q.qualification_id)
from candidats c join cadidats_qualifications q
      on c.candidat_id=q.candidat_id
where q.qualification_id = @q1 or  q.qualification_id=@q2 or q.qualification_id=@q3
group by c.candidat_id
having count(distinct q.qualification_id)>=3
0
 
ralmadaCommented:
sorry like this

select c.candidat_id, count(distinct q.qualification_id)
from candidats c join candidats_qualifications q
      on c.candidat_id=q.candidat_id
where       exists(select 1 from candidats_qualifications where qualification_id = @q1 and q.candidat_id = candidat_id) and
      exists(select 1 from candidats_qualifications where qualification_id = @q2 and q.candidat_id = candidat_id) and
      exists(select 1 from candidats_qualifications where qualification_id = @q3 and q.candidat_id = candidat_id)
group by c.candidat_id
having count(distinct q.qualification_id)>=3
0
 
Lara FEACommented:

ralmada suggestion is incorrect since we do not pull any other qualifications  in this query
where q.qualification_id = @q1 or  q.qualification_id=@q2 or q.qualification_id=@q3
, only those 3  qualifications, which means

count(distinct q.qualification_id)
will be 3 or less. It never can be more then 3



--------------------------------------
So here are more explanation on quesries
-- query from 1-st post will give you candidates with exact set of 3 qualifications. You don't need use profileID

-- query from 2-nd post gives you candidate that have ALL qualifications defined in selected @profileID. It will also bring candidates that have other qualification, not defined in profile

-- this query will give you candidate that have at least 3 of qualification from predefined profile, but does not specify which set of 3. So if profile has 5 qualifications, it will bring candidates that have
1234567
135
123,
34567
etc

select c.candidat_id, count(distinct q.qualification_id)
from candidats c join cadidats_qualifications q
      on c.candidat_id=q.candidat_id
where q.qualification_id in (select qualificationid from profil_qualifications where profile_id =@profileID )
group by c.candidat_id
having count(distinct q.qualification_id)=3


0
 
ralmadaCommented:
>>ralmada suggestion is incorrect since we do not pull any other qualifications  in this query<<
trofimoval, did you read my comment http:#a36893104 ?
0
 
ralmadaCommented:
Now it looks to me that you have two requirements

>>1- Given a profil, I need to select all candidats who have all the required qualifications.<<

In this case use the following query

 
select * from (
	select 	a.candidat_id,
		a.firstname,
		a.lastname,
		b.qualification_id,
		d.profil_id,
		d.profil_name,
		count(candidat_id) over (partition by a.candidat_id) rn
	from candidats a
	inner join candidats_qualification b on a.candidat_id = b.candidat_id
	inner join profils_qualification c on b.qualification_id = c.qualification_id
	inner join profils d on c.profil_id = d.profil_id
) t1
where 	t1.profil_id = @profil and
	t1.rn = (select count(qualification_id) from profil_qualification where profil_id = t1.profil_id)

Open in new window


>>2- I need to able to select the required qualifications from a profil and get the list of candidats. For instance, given a profil requiring 5 qualifications, I need to be able to select 3 required qualifications and get the list of candidats who have these qualifications.<<

For this one try like I've suggested before, that is:

 
select * from 
(
	select 	a.candidat_id,
		a.firstname,
		a.lastname,
		b.qualification_id,
		d.profil_id,
		d.profil_name
	from candidats a
	inner join candidats_qualification b on a.candidat_id = b.candidat_id
	inner join profils_qualification c on b.qualification_id = c.qualification_id
	inner join profils d on c.profil_id = d.profil_id
) t1 
where exists(select 1 from candidats_qualifications where qualification_id = @q1 and candidat_id = t1.candidat_id) and
      exists(select 1 from candidats_qualifications where qualification_id = @q2 and candidat_id = t1.candidat_id) and
      exists(select 1 from candidats_qualifications where qualification_id = @q3 and candidat_id = t1.candidat_id)
where d.profil_id = @profil

Open in new window


or like this

 
select 	a.candidat_id,
		a.firstname,
		a.lastname,
		b.qualification_id,
		d.profil_id,
		d.profil_name
	from candidats a
	inner join candidats_qualification b on a.candidat_id = b.candidat_id
	inner join profils_qualification c on b.qualification_id = c.qualification_id
	inner join profils d on c.profil_id = d.profil_id
	where b.qualification_id =ALL (select qualification_id from profils_qualification where profil_id = @profil_id)

Open in new window

0
 
bkouroumaAuthor Commented:
Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now