Can someone please show me how to re-write this statement for MySQL? I wrote it in Access using the wysiwyg and then copied the SQL. I get a very generic error when I run it, however and cannot figure out what is wrong. I can usually write my own statements, but when I get too many JOINS, I get confused.

I'd appreciate any help.

SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.* FROM (customers INNER JOIN orders ON customers.custID = orders.ocustomerid) INNER JOIN (products INNER JOIN oitems ON products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid WHERE (((orders.orderID)=2))

FROM tablename INNER...

NOT

FROM (tablename INNER ...

I removed the first set of parenthesis:

SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.* FROM customers INNER JOIN orders ON customers.custID = orders.ocustomerid INNER JOIN (products INNER JOIN oitems ON products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid WHERE (((orders.orderID)=2))

But I still get this vague message:

You have an error in your SQL syntax near '(products INNER JOIN oitems ON products.catalogID = oitems.catalogid) ON orders.'

which is where the error was ocurring before as well

INNER JOIN (products
same thing..
should be
INNER JOIN products

yeah, i had tried that after your last suggestion. I tried a bunch of different groupings hoping to hit on something, but no luck

You have an error in your SQL syntax near 'INNER JOIN oitems ON products.catalogID = oitems.catalogid ON orders.orderID = o'

I'm not sure about what you want to do but could you format your query like this... Would make it clearer to you and everyone else...


SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.*

FROM customers

INNER JOIN orders ON customers.custID = orders.ocustomerid
INNER JOIN products ON ??????????????????????
INNER JOIN oitems ON (products.catalogID = oitems.catalogid and orders.orderID = orderid )

WHERE orders.orderID=2


Looks like someth

you, my friend, are a genius!

I think this worked (at least I didn't receive any errors). I now just need to verify that I'm getting the correct values I was looking for. Here's what I am using now:

SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems, customers.*
FROM customers
INNER JOIN orders ON customers.custID = orders.ocustomerid
INNER JOIN products ON oitems.catalogid = products.catalogID
INNER JOIN oitems ON (products.catalogID = oitems.catalogid and orders.orderID = oitems.orderid )
WHERE orders.orderID=2

Thank you so much for looking at this for me. Just looking at this last way that you structured it has actually given me a better understanding of what this query is really doing. Thanks again!