SQL Server Performance

The multi-part identifier could not be bound

Discussion in 'SQL Server 2005 General DBA Questions' started by crazyfrog, Apr 11, 2007.

  1. crazyfrog New Member

    Hi Everyone,I'm using the following query to select data from two tables, basically it gets the related row (according to the product_id) in table2 and then combine the row with the product_id in table1

    subproduct_id is the identifier of table2

    SELECT a.product_id,aa.*
    FROM table1 a
    left outer join
    (select ROW_NUMBER() OVER(ORDER BY subproduct_id) AS 'RowNumber',*
    from table2
    where product_id=a.product_id
    ) aa
    on aa.RowNumber = 1
    and a.product_id= aa.product_id

    Here is the error message I got:
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "a.product_id" could not be bound.


    Can anyone fix this? Thanks in advance.
  2. MohammedU New Member

  3. crazyfrog New Member

    thanks MohammedU, that link is useful and i guess the error is about the alias. However, the query still doesn't work (with the same error message) after making changes.

    SELECT a.product_id,aa.*
    FROM table1 a
    left outer join
    (select ROW_NUMBER() OVER(ORDER BY b.subproduct_id) AS 'RowNumber',*
    from table2 b
    where b.product_id=a.product_id
    ) aa
    on aa.RowNumber = 1
    and a.product_id= aa.product_id

    please correct me if i'm not in the right track, cheers.
  4. MohammedU New Member

    Try the following...
    Can you post some sample data with query...

    SELECT a.product_id,aa.*
    FROM table1 a
    left outer join
    (select ROW_NUMBER() OVER(ORDER BY b.subproduct_id) AS 'RowNumber',*
    from table2 b
    ) aa
    on aa.RowNumber = 1
    and a.product_id= aa.product_id


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. crazyfrog New Member

    thanks MohammedU,

    Here is a simply query to generate two tables:
    create table table1 (product_id int, product_name varchar(20));
    create table table2 (price_id int, product_id int, price_description varchar(20));

    insert into table1 values(1,'car');
    insert into table1 values(2,'bus');

    insert into table2 values(1,1,'$2000');
    insert into table2 values(2,1,'$3000');
    insert into table2 values(3,2,'$4000');


    i want to get the result like:

    product_id price_description
    1 $2000
    2 $4000

    I have to use row_number() because later on I will put more price_description in one row like this:
    product_id price_description price_description price_description price_description
    1 $2000 $3000 $4000 $5000


    I'm currently trying to return the first line according to the different product_id at this stage:

    select a.product_id,aa.price_description
    FROM table1 a
    left outer join (
    select ROW_NUMBER() OVER(ORDER BY b.price_id) AS 'RowNumber',b.*
    from table2 b
    where a.product_id = b.product_id //this line gives the error
    ) aa
    on aa.RowNumber = 1
    and a.product_id= aa.product_id

    but it always gives me "The multi-part identifier "a.product_id" could not be bound." error message.
  6. MohammedU New Member

    If you use aa.RowNumber = 1, I don't think you get the desired results...
    Check the following...

    select a.product_id,aa.price_description
    FROM table1 a
    left outer join (
    select ROW_NUMBER() OVER(ORDER BY b.price_id) AS 'RowNumber',b.*
    from table2 b
    where exists (select product_id FROM table1 a where a.product_id = b.product_id )
    ) aa
    on aa.RowNumber in ( 1 , 3)
    and a.product_id= aa.product_id



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  7. Roji. P. Thomas New Member

    Your query is syntactcally incorrect. You cannot have a correlated reference to column of an Outer tble, inside a derived table,when you are using JOINS. SQL Server 2005 introduces the CROSS APPLY feature for this scenario. So your query should be


    select a.product_id,aa.price_description
    FROM table1 a
    CROSS APPLY (
    select ROW_NUMBER() OVER(ORDER BY b.price_id) AS 'RowNumber',b.*
    from table2 b
    where a.product_id = b.product_id
    ) aa
    WHERE aa.RowNumber = 1
    and a.product_id= aa.product_id


    Roji. P. Thomas
    SQL Server MVP
    http://toponewithties.blogspot.com
  8. crazyfrog New Member

    Thanks MohammedU and Roji, I really appreciate your help. Cheers

Share This Page