When I try to perform a JOIN on a query with multiple tables listed in the from clause I often get the error "The multi-part identifier could not be bound." Is there a specific reason for this?
;with pagination as (SELECT a.*, row_number() over (order by sa_Departmentforms.Changedate desc) as rowNo from (select sa_Forms.*, sa_Department.Department from sa_Forms
left join sa_Departmentforms on sa_Departmentforms.formid=sa_Forms.id
inner join sa_Department on sa_Departmentforms.Departmentid=sa_Department.id
and sa_Departmentforms.Changedate in (select max(Changedate) from sa_Departmentforms where Departmentid=sa_Department.id) ) a)
select *, (select count(*) from pagination) as totalResults FROM pagination
WHERE rowNo between ((2 - 1) * 10) + 1 and 2 * 10