Why does double CTE fail to work in this case?-CodePudding

Here is something that compiles in SQL Server given my initial table Hackers:

WITH temp AS(
    SELECT h.hacker_id as id, MIN(h.name) AS name, COUNT(h.hacker_id) AS n_challenge 
    FROM Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id
)
SELECT * FROM temp
WHERE n_challenge NOT IN (
    SELECT n_challenge FROM temp
    WHERE n_challenge != (SELECT MAX(n_challenge) FROM temp)
    GROUP BY n_challenge
    HAVING COUNT(n_challenge) > 1 
)
ORDER BY n_challenge DESC, id;

However I wanted to make a minor change by putting the block of select:

SELECT n_challenge FROM temp
WHERE n_challenge != (SELECT MAX(n_challenge) FROM temp)
GROUP BY n_challenge
HAVING COUNT(n_challenge) > 1

above the SELECT * FROM temp line using another WITH clause so I can reference that block multiple times. However as I tried this:

WITH temp1 AS(
    SELECT h.hacker_id as id, MIN(h.name) AS name, COUNT(h.hacker_id) AS n_challenge 
    FROM Hackers h JOIN Challenges c ON h.hacker_id = c.hacker_id
    GROUP BY h.hacker_id
),
temp2 AS(
    SELECT n_challenge FROM temp1
    WHERE n_challenge != (SELECT MAX(n_challenge) FROM temp1)
    GROUP BY n_challenge
    HAVING COUNT(n_challenge) > 1 
)
SELECT * FROM temp1
WHERE n_challenge IN temp2
ORDER BY n_challenge DESC, id;

It doesn't compile saying invalid syntax near temp2. I tried enclosing temp2 in bracket and the error said temp2 is invalid column name. Anyone can explain why this fails and solution with storing the select query in a temp name?

CodePudding user response：

You can't use IN with table name or CTE in your case.

Instead you should use subquery like:

IN (SELECT n_challenge FROM temp2)

or you can use joins like:

SELECT t1.* 
FROM temp1 t1
JOIN temp2 t2 ON t1.n_challenge = t2.n_challenge 
ORDER BY t1.n_challenge DESC, id;