Hello, I am having problems with this query, and I'm hoping someone can help me out.
(You may need to maximize your mail client window in order to read these queries
properly, sorry.)
SELECT j.JobID, j.Active, j.JobCode, j.JobName, j.PullDate,
j.PostDate, j.Archived, d.Department, Count(c.ApplicantID) AS ApplicantCount
FROM (T_Jobs j LEFT OUTER JOIN T_Candidates c ON j.JobID = c.JobID)
LEFT OUTER JOIN T_Departments d ON j.DepartmentID = d.DepartmentID
WHERE 1 = 1
<CFIF NOT Archived>AND j.Archived = 0</CFIF>
<CFIF Len(KeyWords)>
<CFLOOP LIST="#KeyWords#" INDEX="Word">
AND (j.JobName LIKE '%#Word#%' OR FullDesc LIKE '%#Word#%' OR
j.JobCode LIKE '%#Word#%')
</CFLOOP>
</CFIF>
<CFIF Len(JobCode)>AND j.JobCode LIKE '%#JobCode#%'</CFIF>
<CFIF DepartmentID>AND j.DepartmentID = #DepartmentID#</CFIF>
<CFIF JobLevelID>AND j.JobLevelID = #JobLevelID#</CFIF>
GROUP BY j.JobID, j.Active, j.JobCode, j.JobName, j.PullDate, j.PostDate, j.Archived,
d.Department
ORDER BY COUNT(c.ApplicantID) DESC, JobName
This query actually works fine, the problem is that I need to pull out Applicants from
T_Candidates ONLY when Archived = 0 in that table. I tried adding AND c.Archived = 0
to the WHERE clause but that seems to cancel out the outer join - so I don't get the
jobs with no candidates! That's no good.
Then, I tried calculating the value with a subquery:
SELECT j.JobID, j.Active, j.JobCode, j.JobName, j.PullDate, j.PostDate, j.Archived,
d.Department,
( SELECT Count(ApplicantID) FROM T_Candidates WHERE JobID = j.JobID AND
Archived = 0 ) AS ApplicantCount
FROM ( T_Jobs j LEFT OUTER JOIN T_Candidates c ON j.JobID = c.JobID )
LEFT OUTER JOIN T_Departments d ON j.DepartmentID = d.DepartmentID
WHERE 1 = 1
<CFIF NOT Archived>AND j.Archived = 0</CFIF>
<CFIF Len(KeyWords)>
<CFLOOP LIST="#KeyWords#" INDEX="Word">
AND (j.JobName LIKE '%#Word#%' OR FullDesc LIKE '%#Word#%' OR
j.JobCode LIKE '%#Word#%')
</CFLOOP>
</CFIF>
<CFIF Len(JobCode)>AND j.JobCode LIKE '%#JobCode#%'</CFIF>
<CFIF DepartmentID>AND j.DepartmentID = #DepartmentID#</CFIF>
<CFIF JobLevelID>AND j.JobLevelID = #JobLevelID#</CFIF>
GROUP BY j.JobID, j.Active, j.JobCode, j.JobName, j.PullDate, j.PostDate, j.Archived,
d.Department
ORDER BY #OrderBy#
That query gives me the correct results, but then I can't sort by the ApplicantCount!
I tried sorting by the subquery, but it seems that that does not work (it returned a
syntax error). As a fluke, I got this query working, where I use the subquery and the
outer join together:
SELECT j.JobID, j.Active, j.JobCode, j.JobName, j.PullDate, j.PostDate, j.Archived,
d.Department,
( SELECT Count(ApplicantID) FROM T_Candidates WHERE JobID = j.JobID AND
Archived = 0 ) AS ApplicantCount
FROM ( T_Jobs j LEFT OUTER JOIN T_Candidates c ON j.JobID = c.JobID )
LEFT OUTER JOIN T_Departments d ON j.DepartmentID = d.DepartmentID
WHERE 1 = 1
<CFIF NOT Archived>AND j.Archived = 0</CFIF>
<CFIF Len(KeyWords)>
<CFLOOP LIST="#KeyWords#" INDEX="Word">
AND (j.JobName LIKE '%#Word#%' OR FullDesc LIKE '%#Word#%' OR
j.JobCode LIKE '%#Word#%')
</CFLOOP>
</CFIF>
<CFIF Len(JobCode)>AND j.JobCode LIKE '%#JobCode#%'</CFIF>
<CFIF DepartmentID>AND j.DepartmentID = #DepartmentID#</CFIF>
<CFIF JobLevelID>AND j.JobLevelID = #JobLevelID#</CFIF>
GROUP BY j.JobID, j.Active, j.JobCode, j.JobName, j.PullDate, j.PostDate, j.Archived,
d.Department
ORDER BY COUNT(c.ApplicantID) DESC, JobName
This seems to return the correct results, at first glance. But really the count it's
sorting by isn't accurate, and there could be a job with 20 archived applicants listed
above a job with 20 non-archived applicants - no good.
If you've gotten this far, thanks for reading! And here's what I think I need: Either:
a way to sort by a subquery, if that's possible, OR, some way to specify a WHERE
condition on ONLY the records from the T_Candidates table, without compromising the
outer join.
My platform is NT4SP5/IIS4/CF4.01/Access97.
Thanks in advance for any help!
Avi
-----
Avi Flax
Web Applications Developer
Words In Progress, Inc.
[EMAIL PROTECTED]
http://www.wordsinprogress.com
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
