• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for 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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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