Selecting Multiple rows, same identifier, same fields,different values 2

[domgreen]

Hi guys, i've been killing myself with this problem. Other programmers in my team don't think this can be done.

The problem being.

I have a table full of applicants, each applicant has at least one record, the difference between each record is the applicant's job_status.

I am trying to select all the applicants where they have a job_status of 5 and 17. Their job_status' are held in the separate rows if you see what i mean.
When i select the applicant by id, i get say 5 records, record 1 shows a job_status = 5, row 2 shows job_status = 8 and row 3 shows job_status = 17 etc etc.

I am really stumped and would appreciate any help.

Thanks in advance.

Dom

 

[Golom]

If I can restate ...

You want to see those applicants that have a record with JobStatus = 5 AND a record with JobStatus = 17.

If so ...

Select ApplicantID, JobStatus, ... other fields ...

From myTable T

Where EXISTS (Select 1 From myTable X 
              Where X.ApplicantID = T.ApplicantID
                AND X.JobStatus = 5)

  AND EXISTS (Select 1 From myTable X 
              Where X.ApplicantID = T.ApplicantID
                AND X.JobStatus = 17)

"ApplicantID" and "JobStatus" should be indexed to improve performance.

 

[r937]

select applicant_id
  from applicants
 where job_status in ( 5, 17 )
group
    by applicant_id
having count(*) = 2

r937.com | rudy.ca

 

[Golom]

Rudy

Nice!!

 

[r937]

thanks golom

this is a very common situation (select something where several rows exist)

for example, select job candidates which have at least 4 of these 7 listed skills

that would be very, very painful to do with EXISTS

with GROUP BY, it's easy -- HAVING COUNT(*) >= 4

r937.com | rudy.ca

 

[domgreen]

Guys thank you sooooo much. Both of the scripts you supplied provided the answer.