Resoving: The multi-part identifier "sa_Departmentforms.Changedate" could not be bound.
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?
thoughts?
thoughts?
;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
where sa_Department.userid=22
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you have taken
left join sa_Departmentforms on sa_Departmentforms.formid=
If formid does not exists it willl return null
otherwise change it to inner join
left join sa_Departmentforms on sa_Departmentforms.formid=
If formid does not exists it willl return null
otherwise change it to inner join
This tuned query would be more optimal
;with pagination as (
SELECT a.*, row_number() over (order by sa_Departmentforms.Changedate desc) as rowNo, count(*) totalResults
from (select sa_Forms.*, sa_Department.Department, row_number() over ( partition by sa_Department.id order by sa_Departmentforms.Changedate desc) rnum
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
where sa_Department.userid=22) a
where rnum = 1)
select *
FROM pagination
WHERE rowNo between 1 and 10
THANKS
ASKER