CODEWITHSUNDEEP
sqlsql-server
AsusT9
AsusT9

Reputation: 154

Sql - NOT IN combined WITH

I'm trying to combine NOT IN with a WITH in T-SQL. But can't get it to work. Is it possible?

Example: 

    select name 
    from Persons
    where id NOT IN 
    (
        WITH result (numbers)
        AS
        (
            select number from num
        )
        select numbers from result
    )

Upvotes: 1

Views: 289

Answers (2)

PowerStar
PowerStar

Reputation: 895

I know you already got the answer, but just want to show the alternate way to solve the same if someone is interested in the future.

SELECT name 
FROM Persons
WHERE id NOT IN (select number from num)

Upvotes: 0

Tobb
Tobb

Reputation: 12180

The WITH keyword cannot be used inside a subquery, it needs to be before the main query.

WITH result (numbers)
AS
(
    select number from num
)
select name 
from Persons
where id NOT IN 
(
    select numbers from result
)

Upvotes: 4

Related Questions