Resoving: The multi-part identifier "sa_Departmentforms.Changedate" could not be bound.

Posted on 2009-12-29
Last Modified: 2012-05-08
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

inner join sa_Department on

 where  sa_Department.userid=22

and sa_Departmentforms.Changedate in (select max(Changedate) from sa_Departmentforms where ) a)

select *, (select count(*) from pagination) as totalResults FROM pagination

WHERE rowNo between ((2 - 1) * 10) + 1 and 2 * 10

Open in new window

Question by:Dhanasekaran Sengodan
    LVL 39

    Accepted Solution

    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
    LVL 14

    Author Comment

    by:Dhanasekaran Sengodan
    this field - (sa_Departmentforms.Changedate)  is not a null value
    LVL 39

    Expert Comment

    by:Pratima Pharande
    you have taken
    left join sa_Departmentforms on

    If formid does not exists it willl return null
    otherwise change it to inner join
    LVL 57

    Expert Comment

    by:Raja Jegan R
    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 order by sa_Departmentforms.Changedate desc) rnum
    from sa_Forms
    left join sa_Departmentforms on
    inner join sa_Department on
    where  sa_Department.userid=22) a
    where rnum = 1)
    select *
    FROM pagination
    WHERE rowNo between 1 and 10

    Open in new window

    LVL 14

    Author Closing Comment

    by:Dhanasekaran Sengodan

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now