CODEWITHSUNDEEP
sqlsql-server-2005
Picflight
Picflight

Reputation: 3852

How do I efficiently use SQL join

Tables

Job

JobId, JobName

JobDetail 

JobDetailId, JobId, AcctNum, PerformDate

One Job has many JobDetails linked by JobId.

For certain JobNames, (JobeName1, JobName2) I want a list with ONLY one JobDetail where Performdate is within last 6 months.

For the criteria above I should get two records. What is the correct SQL statement?

I have gotten as far as this and am not get just getting the correct statement.

SELECT 
    COUNT(J.JobId) 
FROM 
    Job J  
WHERE 
    J.JobName IN ('Doc1', 'Doc2')
GROUP BY 
    J.JobName

[EDIT]
SQL Server 2005

[EDIT2] 

SELECT 
    J.JobName, JD.AcctNum 
FROM 
    Job J  
JOIN JobDetail JD ON J.JobId = JD.JobId
WHERE 
    J.JobName IN ('DOC1', 'DOC2')
GROUP BY 
    J.JobName

This statement is producing:

J.JobId is invalid in the select list...not contained in aggregate function..

[EDIT3]
http://sqlfiddle.com/#!3/3ef21/1/0

I want most recent AcctNum by PerformDate for Doc1 & DOC2.

So, from the data in SQLFIDDLE, I should get 2 ROWS back one for each of the JobName.

[EDIT4]
My expected data is * rows from JobDetail table:
JobDetailId 1
jobDetailId 3

Upvotes: 2

Views: 126

Answers (2)

unlimit
unlimit

Reputation: 3752

Here you go:

SELECT COUNT(J.JobId) FROM Job J  
INNER JOIN JobDetails D ON J.JobId = D.JobId
WHERE J.JobName IN ('Jobname1', 'Jobname2') 
AND D.PerformDate >= DateAdd(mm, -6, getdate())
GROUP BY J.JobName

I have written this in TSQL (SQL Server). For other DBMS you will have to use the relevant function for DateAdd(mm, -6, getdate()).

Update: To pick the latest AcctNum for DOC1 and DOC2.

Select D.JobID, Acctnum from 
    JobDetail D Inner Join
    (
     SELECT J.JOBID, MAX(PerformDate) as LatestPerformDate FROM Job J
     INNER JOIN JobDetail D ON J.JobId = D.JobId
     WHERE J.JobName IN ('DOC1', 'DOC2') 
     Group by J.JobID
    ) X on D.JobID = X.JobID
    And D.PerformDate = X.LatestPerformDate

Update 2: When DOC2 can appear twice in Job table with different JobID.

Select D.JobID, Acctnum from 
JobDetail D Inner Join Job J on J.JobID = D.JobID
Inner join
(
 SELECT J.JOBname, MAX(PerformDate) as LatestPerformDate FROM Job J  
 INNER JOIN JobDetail D ON J.JobId = D.JobId
 WHERE J.JobName IN ('DOC1', 'DOC2')
 group by j.jobname
) X on J.JobName = X.JobName
And D.PerformDate = X.LatestPerformDate

Output from the posted input data in sqlfiddle in the question:

JOBID   ACCTNUM
--------------------
 1      1235
 4      1238

Upvotes: 4

Barmar
Barmar

Reputation: 781726

SELECT j.*, d.*
FROM Job j
JOIN (SELECT JobId, MAX(PerformDate) maxDate
      FROM JobDetail
      WHERE PerformDate > DATEADD(mm, -6, GETDATE())
      GROUP BY JobId) md
ON j.JobId = md.JobId
JOIN JobDetail d
ON d.JobId = md.JobId AND d.PerformDate = md.maxDate
WHERE j.JobName IN ('DOC1', 'DOC2');

FIDDLE

Upvotes: 2

Related Questions