• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 860
  • Last Modified:

The multi-part identifier "i.orderid" could not be bound.

SELECT
p.productid,p.title, (ISNULL(CAST(P_Cnt.prod_Cnt AS VARCHAR(20)), 'No Orders'))as aquantity,p.internalsku,p.internalsku2,p.internalsku3,p.folderid,p.inwarehouse,p.inwarehouse2,p.inwarehouse3,p.ourinwarehouse,p.ourinwarehouse2,p.ourinwarehouse3,p.sentquantity,p.sentquantity2,p.sentquantity3,p.estimatedcost,p.cost2,p.cost3,date,date2,date3,estimateddate,estimateddate2,estimateddate3
from products p
left outer join (
select p2.productid, count(*) prod_Cnt from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)
group by p2.productid)
P_Cnt on p.productid = P_Cnt.productid
where p.folderid = 119 order by p.productid desc

works

but in select statement, when I want to display i.orderid
I get an error





SELECT
p.productid,p.title, (ISNULL(CAST(P_Cnt.prod_Cnt AS VARCHAR(20)), 'No Orders'))as aquantity,p.internalsku,p.internalsku2,p.internalsku3,p.folderid,p.inwarehouse,p.inwarehouse2,p.inwarehouse3,p.ourinwarehouse,p.ourinwarehouse2,p.ourinwarehouse3,p.sentquantity,p.sentquantity2,p.sentquantity3,p.estimatedcost,p.cost2,p.cost3,date,date2,date3,estimateddate,estimateddate2,estimateddate3,i.orderid
from products p
left outer join (
select p2.productid, count(*) prod_Cnt from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)
group by p2.productid)
P_Cnt on p.productid = P_Cnt.productid
where p.folderid = 119 order by p.productid desc


Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "i.orderid" could not be bound.
0
rgb192
Asked:
rgb192
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that's normal, because your subselect you LEFT JOIN to does not return "orderid", AND is not aliases "i" neither

the question is: which row/value or orderid do you want to return?
0
 
rgb192Author Commented:

is there any way to return rows of products table and orderitems table


the query currently only returns products table

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>is there any way to return rows of products table and orderitems table
sure, you only have to define what "join"/relationship you want to apply for the query, aka what output you want.
I don't have a crystal ball, so you have to give some "specs"
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rgb192Author Commented:
i.orderid, i.productid from orderitems table table
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so:


SELECT
p.productid,p.title, (ISNULL(CAST(P_Cnt.prod_Cnt AS VARCHAR(20)), 'No Orders'))as aquantity,p.internalsku,p.internalsku2,p.internalsku3,p.folderid,p.inwarehouse,p.inwarehouse2,p.inwarehouse3,p.ourinwarehouse,p.ourinwarehouse2,p.ourinwarehouse3,p.sentquantity,p.sentquantity2,p.sentquantity3,p.estimatedcost,p.cost2,p.cost3,date,date2,date3,estimateddate,estimateddate2,estimateddate3,i.orderid
from products p
left join orderitems i
  on i.productid= p.productid
left outer join (
select p2.productid, count(*) prod_Cnt from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p2 on i.productid = p2.productid
where o.statusid in (9,10,90,180,260)
group by p2.productid)
P_Cnt on p.productid = P_Cnt.productid
where p.folderid = 119 order by p.productid desc

Open in new window

0
 
rgb192Author 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: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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