Using A CTE to exclude rows from the main Select statement

Question

Using A CTE to exclude rows from the main Select statement

rogue1009

Old Hand

Points: 367
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?

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

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

ScottPletcher SSC Guru Points: 101192 More actions · Answer 1

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".

Using A CTE to exclude rows from the main Select statement

Cookies on SQLServerCentral