How to implement a LEFT OUTER JOIN CLAUSE after WITH AS?

Question

Currently trying to figure out how to implement a SQL LEFT OUTER JOIN while using the SQL WITH AS clause. My code breaks down into 3 SELECT statements while using the same table, then using LEFT OUTER JOIN to merge another table on the id.

I need 3 SELECT statements before joining because I need a SELECT statement to grab the needed columns, ROW RANK the time, and set WHERE clause for the ROW RANK.

SELECT *
    (
    WITH employee AS
        (
        SELECT id, name, department, code, time, reporttime, scheduled_time
        FROM table1 AS a
        WHERE department = "END"
        ),
    employe_v2 as 
        (
        SELECT address
        ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY time desc, reporttime desc, scheduled_time desc) AS row_rank 
        FROM table1 AS b
        )
    SELECT *
    FROM employee, employee_v2
    WHERE row_rank = 1
    ) t1
LEFT OUTER JOIN 
    (
    SELECT b.id, b.new_code, b.date
    FROM table2 AS b
    WHERE b.newcode != "A" 
    ) t2
ON t1.id = t2.id
Group BY t1.id, t1.name, t1.department, t1.code, t1.time, t1.reporttime,
    t1.scheduled_time, t1.row_rank, t2.id, t2.new_code, t2.date

How I could fix my code?

Answer 1

not sure if group by is needed, i see no aggregation whatsover
but if it's something you need , you can add at the end of final select and ofcourse you have to take care of columns/aggregation in select

nevertheless you can simplify your query as below :

with employee as (
   select * from (
      select id, name, department, code, time, reporttime, scheduled_time, address
         ,row_number() over (partition by id order by time desc, reporttime desc, scheduled_time desc) AS row_rank 
     from table1
     ) t where row_rank =1 
)

select t1.*, b.id, b.new_code, b.date
from employee t1
left join table2 as t2
     on t1.id = t2.id
where t2.newcode != "A"