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

Grossnickle, Brenda 60 Reputation points
2024-08-26T20:03:57.4633333+00:00

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

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
102 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 118.4K Reputation points
    2024-08-26T20:52:23.6233333+00:00

    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
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Rodger Kong 360 Reputation points
    2024-08-29T01:54:53.6733333+00:00
    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
    
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.