[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

INNER JOIN (Table INNER JOIN [table] ON...)

I'm stuck... why won't this join work?

tallySql = "select orderitems.ProductId, tblProduct.ItemName, orders.orderstatus, sum(orderItems.Qty) as sumQty from OrderItems INNER JOIN (orders INNER JOIN [tblProduct] ON orderItems.ProductId=tblProduct.ItemSKU) ON orders.orderid=orderitems.orderid WHERE orders.orderstatus = 'a' group by orderItems.ProductId, tblProduct.ItemName order by orderItems.ProductId"
0
mgfranz
Asked:
mgfranz
  • 3
  • 3
1 Solution
 
AzraSoundCommented:
You are joining the [Orders] table and the [tblproduct] table based on criteria that uses the [orderItems] table.  Maybe it should look like this:


tallySql = "select orderitems.ProductId, tblProduct.ItemName, orders.orderstatus, sum(orderItems.Qty) as sumQty from (tblProduct INNER JOIN (orders INNER JOIN OrderItems ON orders.orderid=orderitems.orderid) ON orderItems.ProductId=tblProduct.ItemSKU) WHERE orders.orderstatus = 'a' group by orderItems.ProductId, tblProduct.ItemName order by orderItems.ProductId"
0
 
prokniCommented:
What about this?
select
     orderitems.ProductId, tblProduct.ItemName, orders.orderstatus,      sum(orderItems.Qty) as sumQty
from
     OrderItems,orders,[tblProduct]
WHERE
     orderItems.ProductId=tblProduct.ItemSKU and
     orders.orderid=orderitems.orderid  and
     orders.orderstatus = 'a'
group by
     orderItems.ProductId, tblProduct.ItemName
order by
     orderItems.ProductId"
0
 
mgfranzAuthor Commented:
I get this error with either query;

Category=Microsoft JET Database Engine
Number=(0x80040E21)
Description=You tried to execute a query that does not include the specified expression 'orderstatus' as part of an aggregate function.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
AzraSoundCommented:
add orders.orderstatus to your GROUP BY clause...
0
 
mgfranzAuthor Commented:
Thank you my friend!  :-)

[I don't think I'll ever get the hang of full queries...]
0
 
AzraSoundCommented:
Glad I could help   :-)
0
 
mgfranzAuthor Commented:
I can always rely on some other smart mind to save me...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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