[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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?
;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

Open in new window

0
Dhanasekaran Sengodan
Asked:
Dhanasekaran Sengodan
  • 2
  • 2
1 Solution
 
Pratima PharandeCommented:
order by sa_Departmentforms.Changedate desc

Change this to any unique field which is not null as you have takem left join on sa_Departmentforms
this field might be null
0
 
Dhanasekaran SengodanAuthor Commented:
this field - (sa_Departmentforms.Changedate)  is not a null value
0
 
Pratima PharandeCommented:
you have taken
left join sa_Departmentforms on sa_Departmentforms.formid=sa_Forms.id

If formid does not exists it willl return null
otherwise change it to inner join
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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

Open in new window

0
 
Dhanasekaran SengodanAuthor Commented:
THANKS
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now