Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • 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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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