• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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