sql server - Single SQL Query to Get Task Status -


i appreciate community can offer helping me write sql query achieve following, or, if not possible, offer method so.

objective: return result below:

subtaskid | subtaskqueueid | subtaskname | timecomplete        | jobid | approver  --------- | -------------- | ----------- | ------------------- | ------| -------- 23        | 2              | review #1   | 2017-08-14 00:00:00 | 840   | bill 24        | 2              | review #2   | 2017-08-14 01:00:00 | 840   | john 25        | 2              | review #3   | null                | 840   | null 26        | 2              | review #4   | null                | 840   | null 

the source tables:

job_subtasks_status

jobid | subtaskid    | timecomplete        | approver ----- | ------------ | ------------------- | -------- 840   | 23           | 2017-08-14 00:00:00 | bill 840   | 24           | 2017-08-14 01:00:00 | john 

job_subtasks

subtaskid | subtaskqueueid | subtaskname --------- | -------------- | ----------- 23        | 2              | review #1 24        | 2              | review #2 25        | 2              | review #3 26        | 2              | review #4 27        | 4              | funding approval 28        | 4              | requirement approval 

the important bit note want return records job_subtasks when subtaskqueueid=2, , nulls if there no record in job_subtasks_status (a null indicates subtask not completed).

i thought standard left join do, tried following query:

select a.subtaskid, a.subtaskqueueid, a.subtaskname, b.timecomplete, b.jobid, b.approver  job_subtasks left join job_subtasks_status b on a.subtaskid=b.subtaskid  b.subtaskid=2 , b.jobid=840 

you can try using left outer join. return values from "job_subtasks" table subtaskqueueid=2

select     a.subtaskid,             a.subtaskqueueid,             a.subtaskname,             b.timecomplete,             b.jobid,             b.approver        job_subtasks         left outer join job_subtasks_status b  on  a.subtaskid = b.subtaskid                                    , b.subtaskid = 2                                    , b.jobid = 840 

the result should be:

subtaskid | subtaskqueueid | subtaskname | timecomplete        | jobid | approver  --------- | -------------- | ----------- | ------------------- | ------| -------- 23        | 2              | review #1   | 2017-08-14 00:00:00 | 840   | bill 24        | 2              | review #2   | 2017-08-14 01:00:00 | 840   | john 25        | 2              | review #3   | null                | 840   | null 26        | 2              | review #4   | null                | 840   | null 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -