Having trouble with group by query...

[fatdog302]

Hi,

Trying to return a list of distinct applicantid where they are only linked to jobs with jobStatusID of 2,4,5 if they are link to any jobs with jobstatusid of (1,3) then that applicant should not be included in the list.

ApplicationsTable
Applicantid
JobId


JobsTable
JobID
JobStatusID

Obviously these two tables are linked by jobid.


Thanks
Costa

 

[hindsjl]

I can't test this just now, but try

select distinct a.applicantid
from ApplicationsTable a
inner join JobsTable b
on a.JobId=b.JobId
left join
(select JobId from JobsTable
where JobStatusID in (1,3)
) c
on a.JobId=c.JobId
where b.JobStatusID in (2,4,5)
and c.JobId is null

If it doesn't work this way, try wrapping the inner join in parentheses to separate it from the left join:
from (Applic....
... on a.JobId=b.JobId)

John

 

[fatdog302]

It still returns applicant with application to job type 3.

 

[hindsjl]

Sorry.

select distinct a.applicantid
from ApplicationsTable a
inner join JobsTable b
on a.JobId=b.JobId
where b.JobStatusID in (2,4,5)
and JobId not in
(select JobId from JobsTable
where JobStatusID in (1,3)
)

John

 

[hindsjl]

Left out the a.

select distinct a.applicantid
from ApplicationsTable a
inner join JobsTable b
on a.JobId=b.JobId
where b.JobStatusID in (2,4,5)
and a.JobId not in
(select JobId from JobsTable
where JobStatusID in (1,3)
)

John

 

[fatdog302]

Believe it or not its still returning some wrong applicants. It wont reurn applicants with 1 & 3 only but will return applicants that have 1 or 3 together with 2,4 or 5.

 

[hindsjl]

OK. In my "not in", I was excluding JobIDs, when I should have been excluding ApplicantIDs.

select distinct a.applicantid
from ApplicationsTable a
inner join JobsTable b
on a.JobId=b.JobId
where b.JobStatusID in (2,4,5)
and a.applicantid not in
(select c.applicantid
from ApplicationsTable c
inner join JobsTable d
on c.JobId=d.JobId
where d.JobStatusID in (1,3)
)

Whew!
John

 

[fatdog302]

Looks Good!!!!!
Thanks for your persistence.