Joining two select statements together with outer join

My query on SQL Server:

select 
    s.learners_id, s.cv_student_id, 
    s.first_name + ' ' + s.last_name student_name,
    p.program_name, dc.fulldate program_start_date, 
    sd.discount_value, dt.type_name
from
    fact_student_programs_t sp 
left join 
    object_sTATUSES_T os on os.object_statusid = sp.status_id
inner join 
    dim_programs_t p on p.programs_sk_id = sp.programs_sk_id
left join 
    dim_calendar dc on dc.datekey = sp.start_date_key
inner join 
    dim_students_t s on s.students_sk_id = sp.students_sk_id
outer join 
    (select 
         s.learners_id, sd.discount_value, dt.type_name
     from 
         fact_student_discountS_T sd 
     inner join 
         discount_types_t dt on dt.id = sd.discount_type_id
     inner join 
         dim_students_t s on s.students_sk_id = sd.students_sk_id
     where 
         sd.curr_in = 1) discounts on discounts.learners_id = s.learners_id
where 
    sp.curr_in = 1  
    and dc.fulldate is not null 
    and os.status_name in ('Active') 
    and s.learners_id in ('201328', '237744', '237817', '239826', '308486', '308961',
                          '308973', '309352', '311521', '312269', '312951',
                          '313254', '313289', '384170', '384224', '384228', 
                          '408911', '408912', '408936', '411293', '411308',
                          '411322', '411324', '411325', '411352', '411413',
                          '411417', '412865', '412923')

I am trying to join these two queries and am having trouble. Is the outer join statement in the wrong place? Can someone please help me fix this code?

EDIT: outer join is not valid syntax here, so I am using left join. The query still returns an error:

Msg 4104, Level 16, State 1. The multi-part identifier "dt.type_name" could not be bound. Msg 4104, Level 16, State 1. The multi-part identifier "sd.discount_value" could not be bound. (Line 2)

Answer

Summary

outer join is not valid in SQL Server/t-sql I believe. It should either be left outer join, right outer join or full outer join.

In your situation, I suspect you want it to be a left outer join.

Explanation/longer version

In left and right outer joins, the ‘left’ and ‘right’ refer to the tables/etc literally to the left and right on the join (e.g., before and after the join, respectively).

• In a left outer join, it takes all the values from the table on the left (first table) and any matching rows in the table on the right
• In a right outer join, it takes all the values from the table on the right (second table) and any matching rows in the table on the left

A full outer join gets all rows from both tables, and matches them when they can.

Here is an example

/* Data setup */
CREATE TABLE #T1 (T1_ID int);
CREATE TABLE #T2 (T2_ID int);
INSERT INTO #T1 (T1_ID) VALUES (1), (2);
INSERT INTO #T2 (T2_ID) VALUES (1), (3);

/* Example joins */
SELECT #T1.T1_ID, #T2.T2_ID
FROM #T1
LEFT OUTER JOIN #T2 ON #T1.T1_ID = #T2.T2_ID;

SELECT #T1.T1_ID, #T2.T2_ID
FROM #T1
RIGHT OUTER JOIN #T2 ON #T1.T1_ID = #T2.T2_ID;

SELECT #T1.T1_ID, #T2.T2_ID
FROM #T1
FULL OUTER JOIN #T2 ON #T1.T1_ID = #T2.T2_ID;

/* Results
-- LEFT OUTER JOIN
T1_ID   T2_ID
1       1
2       NULL

-- RIGHT OUTER JOIN
T1_ID   T2_ID
1       1
NULL    3

-- FULL OUTER JOIN
T1_ID   T2_ID
1       1
2       NULL
NULL    3
*/

Given you have WHERE s.learners_id in (...) in your WHERE clause, it implies you do not want rows where s.learners would be NULL.

• If you had a right outer join, that WHERE requirement will effectively turn the right outer join into an inner join (as it would exclude all rows where s.learners_id is NULL).
• If you had a full outer join, that WHERE requirement would effectively turn the full outer join into a left outer join – along similar logic lines.