Solved

SQL SERVER QUERY

Posted on 2011-09-28
9
239 Views
Last Modified: 2012-05-12
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
Comment
Question by:bkourouma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 11

Expert Comment

by:Larissa T
ID: 36718587
--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
 
LVL 11

Expert Comment

by:Larissa T
ID: 36718793
-- 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
 
LVL 1

Author Comment

by:bkourouma
ID: 36815682
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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
LVL 41

Expert Comment

by:ralmada
ID: 36893088
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
 
LVL 41

Expert Comment

by:ralmada
ID: 36893104
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
 
LVL 11

Expert Comment

by:Larissa T
ID: 36893205

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
 
LVL 41

Expert Comment

by:ralmada
ID: 36893268
>>ralmada suggestion is incorrect since we do not pull any other qualifications  in this query<<
trofimoval, did you read my comment http:#a36893104 ?
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 36893994
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
 
LVL 1

Author Closing Comment

by:bkourouma
ID: 36910213
Thanks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question