Using CTE Result with IN in sql server

July 11, 2023

I am trying to find employees names with title who don’t receive any bonus and I get the following error:

WITH Result
AS
(
Select A.assoc_id  from Employee A where assoc_id NOT IN
(select  B.assoc_ref_id from bonus B) 
)

select E.firstname, E.lastname, T.assoc_title from Employee E inner join Title T on  E.assoc_id = T.assoc_ref_id
where E.assoc_id IN (Result ) /*(Result ) gives error*/

HowEver, This works fine for me and I get the desired result:

WITH Result
AS
(
select E.firstname, E.lastname, T.assoc_title from Employee E inner join Title T on  E.assoc_id = T.assoc_ref_id
where E.assoc_id IN (Select A.assoc_id  from Employee A where assoc_id NOT IN
(select  B.assoc_ref_id from bonus B)  )
)
select * from Result

What wrong Am I doing in 1st query? Can we use joins while querying CTE Result at all?

Thanks.

>Solution :

You are fundamentally not understanding how to use a table expression.

You need:

where E.assoc_id in (select assoc_id from Result);

A bit more explanation – A CTE, just like a sub-query or view, produces a logical table expression; and, just like any other table, you can’t just reference it since that does not make any sense to the optimizer, just like any other table you query it by selecting from it. In addition, in many cases the optimizer can and will expand the table expression to be logically part of the main query (ie not a distinctly executed and self-contained object).