Invalid object name in a XML Path / Stuff combination

Question

I am trying to get some values from different rows into a single column, and I keep getting this error :

Invalid object name 't'

The query is rather big and complicated so I narrowed it down to a simple part that still gives me the error.

select 
   IDs = stuff( ( select ',' + convert(varchar, t2.ChassisID)
                  from   t as t2
                  where  t2.ChassisID = 42 --t.ChassisID
                  for XML path('')
                )
                , 1, 1, '' 
              )
from   ( select ch.ChassisID, p.GPS 
         from   tblChassis ch
           inner join tblPlace p on ch.BestemmingID = p.PlaceID
       ) t        
group by t.Gps

I tried changing the where clause to a fixed number (42) instead of t.ChassidID and still get the error, so there is only one place left that could cause the error I assume, but I cant see why.

I probably am missing something simple but I just cannot see it.
What is wrong with this query ?

I am using Sql Server 2014

Answer 1

Try declaring your filtered table in a CTE, then referencing this CTE both times.

;WITH FilteredChassis AS 
(
     select 
        ch.ChassisID, 
        p.GPS 
     from   
        tblChassis ch
        inner join tblPlace p on ch.BestemmingID = p.PlaceID
)
select
    t.Gps,
   IDs = stuff( ( select ',' + convert(varchar, t2.ChassisID)
                  from   FilteredChassis as t2
                  where  t2.Gps = t.Gps
                  for XML path('')
                )
                , 1, 1, '' 
              )
from
    FilteredChassis AS t
group by 
    t.Gps

I've made the link through gps, I believe that's what you need.