CODEWITHSUNDEEP
sqlsql-serverwith-statement
Mr.Qbs
Mr.Qbs

Reputation: 152

SQL Server : With in Select in NOT IN

I Have problem with this code. I have tree in my data base and i need to select all nodes except The Node and his children, grandchilder etc. I done WITH query - it works. I can select all big family of The Node. But when i try to do SELECT ... WHERE id NOT ID (and here is the big family) i have errors...

CODE:

Select * from TALBE 
where id NOT IN
(
    WITH TempTable 
    AS
    (
        SELECT ...
        UNION ALL
        SELECT ...
    )
    Select id from TempTable;
);

or another version

Select * from TALBE 
where id NOT IN
(
    select id from
    (
        WITH TempTable 
        AS
        (
            SELECT ...
            UNION ALL
            SELECT ...
        )
        Select id from TempTable
    )
);

In this code I need to have first 2 lines. Its not my decision - just must to have.

Errors:

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near ')'.

Upvotes: 0

Views: 84

Answers (3)

Sean Lange
Sean Lange

Reputation: 33580

Something like this. You should probably read the documentation about CTEs so you understand what they are and how they are used.

Select * from TALBE 
where id NOT IN
(
    SELECT ID from someTable
    UNION ALL
    SELECT ID from SomeOtherTable
);

Now that it sounds like you want this as a recursive cte you will need to adjust this a little bit.

WITH TempTable 
AS
(
    SELECT ...
    UNION ALL
    SELECT ...
)

Select * from TALBE 
where id NOT IN
(
    SELECT ID from TempTable
);

Upvotes: 0

Radu Porumb
Radu Porumb

Reputation: 785

The way this code is written, the CTE query will be run for every row you're testing since a CTE is not really a result set, but a statement. This is very likely to cause performance issues down the road. A better way would be to cache the results of your query in a table in memory and use them when doing your actual select, like so:

DECLARE @badIds TABLE
(
    Id INT NOT NULL --replace the INT with whatever type your Id column is
)

INSERT INTO @badIds
SELECT someId
FROM someTable
UNION ALL
SELECT someOtherId
FROM someOtherTable
...

SELECT * FROM Table WHERE id NOT IN (SELECT Id FROM @badIds)

Upvotes: 0

jtimperley
jtimperley

Reputation: 2544

As the comments have suggested, you need to remove the CTE or move it to the beginning of you're query.

http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

WITH TempTable 
AS
(
     SELECT ...
     UNION ALL
     SELECT ...
)
Select * from TALBE 
where id NOT IN
(
    select id from TempTable
);

Upvotes: 4

Related Questions