MySQL query multiple table inner join

Question

I have this MySQL query but it seems to be getting an error while I try to run it. Since I'm a newbie I'd like some advice of what I should do to correct it. I just want to show the name, quantity and order date of the orders that has 1 or more pending products. Thanks a lot!

select product.name, order_details.quantity, order.date from product,order_details,order 
inner join order on order_details.order_id=order.id 
inner join product on order_details.product_id=product.id 
inner join customer on order.cust_id=costumer.id WHERE order.pending=>1

If the pending information is in the order table, how do you know which products are still pending ? — xlecoustillier, Jan 09 '13 at 11:25

score 2 · Accepted Answer · answered Jan 09 '13 at 11:14

You have a table called order. This word has special significance in SQL. Your options are to rename the table, or quote it whenever you want to query from it.

Easiest solution is to change.

inner join order ....

to

inner join `order`

Be sure to use back-quotes around the table name.

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

You have a table named 'order', which is a reserved word in SQL.

One solution is to prefix the table name with the database name as explained in Craic Computing blog Another one is to wrap the table name with the ` character as you can read in this StackOverflow question

edited May 23 '17 at 12:04

Community

1
1

answered Jan 09 '13 at 11:14

ederbf

1,713
1
13
18

xlecoustillier · Answer 3 · 2013-01-09T13:17:48.330

You can try something like :

SELECT product.name, order_details.quantity, `order`.date
FROM product
INNER JOIN order_details ON product.id = order_detail.product_id
INNER JOIN `order` ON `order`.id = order_detail.order_id
WHERE `order`.pending >= 1

As said in other answers, orderis a reserved keyword in SQL, surround it with backquotes.

Maybe you should store the pending information in the order_detail table (1 if pending, 0 if not), in order to keep track of which product is still pending instead of incrementing/decrementing the order.pending field.

In this case, you could make the following query :

SELECT product.name, order_details.quantity, `order`.date
FROM product
INNER JOIN order_details ON product.id = order_detail.product_id
INNER JOIN `order` ON `order`.id = order_detail.order_id
WHERE `order_detail`.pending = 1

Which would return all the products still pending in your orders instead of every product from orders in which maybe only one is pending.

You are right with your comment above. I get no results.. What should I do, any suggestions? I'm a total newbie. — Js30, Jan 09 '13 at 11:42
I edited my answer to try to answer your question. Hope this helps. — xlecoustillier, Jan 09 '13 at 13:18

MySQL query multiple table inner join

3 Answers3