MySQL throwing error on second JOIN

Question

I'm getting the following error:

#1064 - 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 'JOIN product_catalog ON product_catalog.entity_id

As a result of the following query:

SELECT sales_order.created_at , order_item.order_id, sales_order.increment_id, SUM(order_item.qty_ordered) AS qty_ordered , COUNT( * ) 

FROM order_item

JOIN sales_order
ON sales_order.entity_id = order_item.order_id
WHERE sales_order.created_at > '2012-11-15 00:00:00'

JOIN product_catalog
ON product_catalog.entity_id = order_item.product_id
WHERE product_catalog.size = 14

GROUP BY order_item.order_id;

Variations on this query have worked for grouping different types of product by sales order in the past where I only needed to perform one JOIN to get all the info I needed. The problem I'm encountering is from the second JOIN. Clearly I'm missing something but I really am not sure what. :(

Answer 1

Please make sure that WHERE condition must be after all JOIN

SELECT sales_order.created_at , order_item.order_id, sales_order.increment_id, SUM(order_item.qty_ordered) AS qty_ordered , COUNT( * ) 

FROM order_item

JOIN sales_order
ON sales_order.entity_id = order_item.order_id

JOIN product_catalog
ON product_catalog.entity_id = order_item.product_id

WHERE product_catalog.size = 14
AND sales_order.created_at > '2012-11-15 00:00:00'
GROUP BY order_item.order_id;

First of all you have to JOIN your tables which you need. Then after WHERE clause come for conditions.

Answer 2

Your WHERE clauses are in the wrong spots. See the code below for proper JOIN syntax.

SELECT sales_order.created_at, 
    order_item.order_id, 
    sales_order.increment_id, 
    SUM(order_item.qty_ordered) AS qty_ordered, 
    COUNT( * )
FROM order_item
JOIN sales_order
    ON sales_order.entity_id = order_item.order_id
    AND sales_order.created_at > '2012-11-15 00:00:00'
JOIN product_catalog
    ON product_catalog.entity_id = order_item.product_id
    AND product_catalog.size = 14
GROUP BY order_item.order_id