SQL Server Performance Forum – Threads Archive

The multi-part identifier could not be bound

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.

Check the following tread…
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=374291&SiteID=1
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

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.

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.

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.

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.

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

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

]]>