Hi,I have three tables

order
Order_ID
OrderDate
Dispatched (either 0 or 1)
....

orderitem
OrderItem_ID
Order_ID
Qty
Product_ID

product
Product_ID
Name
Description
Price
...

I want to get a list of all the products within each order given a particular Order_ID.

So say the Order_ID is 35, I want to return for each order item within it the qty, product name,description and price.

At the moment I've tried

But I'm just getting an error:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order INNER JOIN orderitem ON order.Order_ID = orderitem.Order_ID IN' at line 7"

Find this join stuff really hard, any ideas what I'm doing wrong here?

Thanks

order is a reserved word, so you need to quote youre table name:

you understand joins quite well, your query is fine

the problem is that ORDER is a reserved word

change the name of your table

many database designers prefer to use plural table names, since the idea is that a table is not a thing, but rather, a set of rows

so orders would work nicely

shammat, quoting with doublequotes will work in mysql only if the appropriate server setting is switched on

mysql likes to use the horrid backtick character -- `order` -- but whether doublequotes or backticks are used, you have to remember to do it everywhere, all the time

renaming the table is a more suitable solution

Are there really people out there using MySQL without the ANSI flag?

Of course you are right, I always forget that I'm running MySQL in a extremely un-common way...

better to learn the lesson now on reserved words, and pay the pain of making the changes now rather than let a kludgy workaround remain in perpetuity

thanks guys, turns out `order` worked;-), but think ill change it to order as that is indeed annoying and inconsistent- thanks

Hi

I think this is helpful for u

SELECT
product.name,
product.description,
product.price,
orderitem.quantity
FROM
product JOIN orderitem
ON product.product_id = orderitem.product_id
JOIN orderitem
ON orderitem.order_id = order.order_id
WHERE
Order.order_id = 35;

it is not

that query will produce a syntax error on order.order_id

can you guess why?

because your query does not include the order table

SELECT
product.name,
product.description,
product.price,
orderitem.quantity,order.order_id
FROM
product JOIN orderitem
ON product.product_id = orderitem.product_id
JOIN orderitem
ON orderitem.order_id = order.order_id
WHERE
Order.order_id = 35;

not, it is not right

keep trying, you'll eventually get it

by the way, you're not testing these queries, you're just writing them out of thin air, correct?