Using A CTE to exclude rows from the main Select statement

• rogue1009

  Old Hand

  Points: 365

  More actions

  February 22, 2023 at 2:33 pm

  #4152811

  I have a cte that in short states

  SELECT 
  mm.id1
  mm.date
  ,mm.Datecompleted
  ,mn.number
    FROM table1 as mm
    left join table2 as m
    on mm.type = m.type
    left join table3 as mn
    on mm.id1 = mn.id1
    where
    1=1
    and type = 29
    and date is null
    and Completed = 1

  I then have a select statement which I left outer join the cte too. Want I want to do is in the final result set exclude any mn number that appears in the cte. I have tried not in and it does not work. Any ideas?

   

   

• ScottPletcher

  SSC Guru

  Points: 100949

  More actions

  February 22, 2023 at 2:48 pm

  #4152816

  That code is not valid.  There is no way to tell which 'type' column is being referenced in the WHERE clause.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.