Post reply

Using A CTE to exclude rows from the main Select statement

  • 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?

     

     

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply