Join to return default job information if step information is not present

Question

I have a job 1111 with three steps : 11111, 11112, 11113. Steps 11111 and 11112 have a specific delivery method. Step 11113 does not have a specific delivery method so it will use the job default delivery. I have tried to code it my self but the code is a mess. Can someone help me with the tsql code to get my expected results.

drop table job_step
drop table delivery_information

create table job_step (job_cmid int, job_step_cmid int)
create table delivery_information (cmid int, delivery varchar(250))

insert into job_step (job_cmid, job_step_cmid)
values 
(1111, 11111),
(1111, 11112),
(1111, 11113)

insert into delivery_information (cmid, delivery)
values 
(1111,  'save to f:\aaa'),  -- default for job 1111 if there is not a jobstep specific override
(11111, 'send to peggy'),
(11112, 'hold for john'),
(2222,  'send letter'),
(22221, 'do nothing')

expected results

job_step delivery

1 send to peggy

2 hold for john

3 save to f:\aaa

Answer 1

Show your code and try this:

select coalesce(i.delivery, i2.delivery) as job_step_delivery
from job_step s
left join delivery_information i on i.cmid = s.job_step_cmid
left join delivery_information i2 on i2.cmid = s.job_cmid
where s.job_cmid = 1111
order by s.job_step_cmid

Answer 2

SELECT js2.job_cmid, js2.sn, js2.new_job_step_cmid, di2.delivery FROM
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY job_cmid ORDER BY job_step_cmid) sn 
    ,job_cmid 
    ,ISNULL(di.cmid, js.job_cmid) new_job_step_cmid 
  FROM 
    job_step js 
  LEFT JOIN 
    delivery_information di 
  ON js.job_step_cmid = di.cmid
)js2
INNER JOIN 
  delivery_information di2 
ON js2.new_job_step_cmid = di2.cmid
ORDER BY js2.job_cmid, js2.sn