Solved

SQL SERVER QUERY

Posted on 2011-09-28
9
195 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now