September 30, 2005 at 9:15 am
I have 2 tables:
*Job_Posting_Table : contains each job posted from company
*Mgmt_App_Detail_Table :contains all applications received from the recruiting website.
'QUERY #1
SELECT Job_Code, Job_Title, JobPosting_ID, Job_Location
FROM Job_Posting_Table
JobPosting_ID Job_Code Job_Title Job_Posting_Date Job_Location_Type Job_Location
1 3 Assistant Manager 10/26/2004 Store BK00209
2 3 Shift Coordinator 11/1/2004 Store BK01799
3 1 Team Member 3/25/2005 Store BK02164
4 5 District Manager 5/4/2005 District District 1009
5 6 Regional Director 5/9/2005 Market Raleigh
6 6 Regional Director 5/9/2005 Market Chicago South
'QUERY #2
SELECT Count(*)
FROM Mgmt_App_Detail_Table
where job_posting_id=1
Applicant_ID Received_Date Applied_Location JobPosting_ID App_Avail_StartDate
58 7/15/2005 Chicago North 1 8/1/2005
I need to query how many applications received for a particular jobposting_id and the information about this job (job_code, job_title, job_location)
How to combine this 2 queries to 1?
Thank you.
September 30, 2005 at 9:23 am
SELECT JPT.JobPosting_ID,
MADT.ApplicationsReceived,
JPT.Job_Code,
JPT.Job_Title,
JPT.Job_Posting_Date,
JPT.Job_Location_Type,
JPT.Job_Location
FROM
Job_Posting_Table JPT
JOIN
(
SELECT JobPosting_ID, COUNT(*) ApplicationsReceived
FROM
Mgmt_App_Detail_Table
GROUP BY JobPosting_ID) MADT
ON
JPT.JobPosting_ID = MADT.JobPosting_ID
Regards,
gova
September 30, 2005 at 11:06 am
Thank you govinn. It works perfectly.
September 30, 2005 at 1:07 pm
Use a left join instead of an inner join.
SELECT JPT.JobPosting_ID,
ISNULL(MADT.ApplicationsReceived,0),
JPT.Job_Code,
JPT.Job_Title,
JPT.Job_Posting_Date,
JPT.Job_Location_Type,
JPT.Job_Location
FROM
Job_Posting_Table JPT
LEFT JOIN /*jobpostings can be without madt, ApplicationsReceived will be null then*/
(
SELECT JobPosting_ID, COUNT(*) ApplicationsReceived
FROM
Mgmt_App_Detail_Table
GROUP BY JobPosting_ID) MADT
ON
JPT.JobPosting_ID = MADT.JobPosting_ID
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply