Joining Multiple Tables With Left Join/ Outer Join

Question

Hi I'm having trouble understanding join with multiple tables. I'm trying to get all data for each tables (Schedule,Forecast,Ideal,Actual). The query below would omit data from vendor..ideal_labor if the same data is not in the edw..f_labor_dtl. For example, if there is no data from edw..f_labor_dtl on day '2019-02-07', the query would also omit from vendor..ideal_labor even if it has data on '2019-02-07'. This is easy to fix if I only have 2 tables joining eaching other (use left/right join or full outer join). Here with multiple tables, I'm using my 3 dimensions table as a left join (SB_Finance.dbo.d_Job_Detail, edw..d_date, edw..d_store). Meaning there would always be day '2019-02-07' in edw..d_date. How could when I left join on edw..d_date, it performed like an inner join on all the temp table?

with 
fcst as (
    select
        DOB as business_date,
        unit as store_id,
        JobCode as job_code,
        sum(Hours) as Hours
    from vendor..HotSchedulesOptimalsForecast
    group by dob,unit,JobCode
),
schd as (
    select
        DOB as business_date,
        unit as store_id,
        JobCode as job_code,
        sum(ScheduledMinutes/60.0) as Hours
    from vendor..HotSchedulesScheduled
    group by dob,unit,JobCode   
),
ideal as (
    select
        business_date,
        store_id,
        job_code,
        sum(ideal_hrs) as Hours
    from vendor..ideal_labor
    group by business_date,store_id,job_code    
),
act as (
    select
        business_date,
        store_id,
        job_code,
        sum(ttl_hrs) as Hours
    from edw..f_labor_dtl
    group by business_date,store_id,job_code    
)
select
    d.business_date,
    d.f_year,
    d.f_period,
    d.week_period,
    d.week_year,
    d.week_ending,
    s.store_id,
    s.store_desc,
    s.rpt_concept,
    s.region,
    j.job_code,
    j.job_name,
    j.Cat_OPKPI,
    sum(schd.Hours) as Schd_Hrs,
    sum(fcst.Hours) as Fcst_Hrs,
    sum(act.Hours) as Actl_Hrs,
    sum(ideal.Hours) as Ideal_Hrs
from SB_Finance.dbo.d_Job_Detail j
    left join fcst on j.job_code = fcst.job_code
    left join schd on j.job_code = schd.job_code
    left join ideal on j.job_code = ideal.job_code
    left join act act on j.job_code = act.job_code
    left join edw..d_date d on d.business_date = fcst.business_date and d.business_date = schd.business_date and d.business_date = ideal.business_date and d.business_date = act.business_date
    left join edw..d_store s on s.store_id = fcst.store_id and s.store_id = schd.store_id and s.store_id = ideal.store_id and s.store_id = act.store_id
where
    is_closed = 0
    and f_year = 2019
    and Cat_OPKPI in ('Service','Culinary','Support','Janitorial')
group by
    d.business_date,d.f_year,d.f_period,d.week_period,d.week_year,d.week_ending,s.store_id,s.store_desc,s.rpt_concept,s.region,j.job_code,j.job_name,j.Cat_OPKPI

Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what the inner join on is. Also: For code questions give a [mcve]. And part of that is a clear specification. Here you don't say what result you want in terms of inputs and you just give code that is wrong. — philipxy, Feb 14 '19 at 02:55

score 0 · Answer 1 · answered Feb 14 '19 at 02:28

I found the answer after playing around with join. I'm using cross join on all dimensions table then use left join to connect the other data tables.

with 
fcst as (
    select
        DOB as business_date,
        unit as store_id,
        JobCode as job_code,
        sum(Hours) as Hours
    from vendor..HotSchedulesOptimalsForecast 
    group by dob,unit,JobCode
),
schd as (
    select
        DOB as business_date,
        unit as store_id,
        JobCode as job_code,
        sum(ScheduledMinutes/60.0) as Hours
    from vendor..HotSchedulesScheduled
    group by dob,unit,JobCode   
),
ideal as (
    select
        business_date,
        store_id,
        job_code,
        sum(ideal_hrs) as Hours
    from vendor..ideal_labor
    group by business_date,store_id,job_code    
),
act as (
    select
        business_date,
        store_id,
        job_code,
        sum(ttl_hrs) as Hours
    from edw..f_labor_dtl
    group by business_date,store_id,job_code    
), summary as (
select
    d.business_date,
    d.f_year,
    d.f_period,
    d.week_period,
    d.week_year,
    d.week_ending,
    s.store_id,
    s.store_desc,
    s.rpt_concept,
    s.region,
    j.job_code,
    j.job_name,
    j.Cat_OPKPI,
    isnull(sum(schd.Hours),0) as Schd_Hrs,
    isnull(sum(fcst.Hours),0) as Fcst_Hrs,
    isnull(sum(act.Hours),0) as Actl_Hrs,
    isnull(sum(ideal.Hours),0) as Ideal_Hrs
from SB_Finance.dbo.d_Job_Detail j
    cross join edw..d_date d
    cross join edw..d_store s
    left join fcst on j.job_code = fcst.job_code and d.business_date = fcst.business_date and s.store_id = fcst.store_id
    left join schd on j.job_code = schd.job_code and d.business_date = schd.business_date and s.store_id = schd.store_id
    left join ideal on j.job_code = ideal.job_code and d.business_date = ideal.business_date and s.store_id = ideal.store_id
    left join act on j.job_code = act.job_code and d.business_date = act.business_date and s.store_id = act.store_id

where
    is_closed = 0
    and d.f_year = 2019
    and Cat_OPKPI in ('Service','Culinary','Support','Janitorial')
group by
    d.business_date,d.f_year,d.f_period,d.week_period,d.week_year,d.week_ending,s.store_id,s.store_desc,s.rpt_concept,s.region,j.job_code,j.job_name,j.Cat_OPKPI

Joining Multiple Tables With Left Join/ Outer Join

1 Answers1