Mysql error that I don't expect

Question

Error:

Unknown column 'miahrose_pos104.phppos_sales.item_id' in 'on clause'

I don't think this should happen because I am joining phppos_sales_items to phppos_items, but it seems the query thinks it is being joined to phppos_sales. What am I doing wrong?

Query:

CREATE TEMPORARY TABLE phppos_sales_items_temp 
(SELECT date(sale_time) as sale_date, phppos_sales_items.sale_id, comment,payment_type, customer_id, employee_id, phppos_items.item_id, supplier_id, quantity_purchased, item_cost_price, item_unit_price, SUM(percent) as item_tax_percent, discount_percent, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) as subtotal, ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(1+(SUM(percent)/100)),2) as total, ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(percent)/100),2) as tax, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) - (item_cost_price*quantity_purchased) as profit FROM ((((
phppos_sales_items 
INNER JOIN phppos_sales USING (sale_id)) 
INNER JOIN phppos_items USING (item_id)) 
LEFT OUTER JOIN phppos_suppliers ON phppos_items.supplier_id=phppos_suppliers.person_id) LEFT OUTER JOIN phppos_sales_items_taxes USING (sale_id, item_id)) 
GROUP BY sale_id, item_id)

Answer 1

I don't think this should happen because I am joining phppos_sales_items to phppos_items, but it seems the query thinks it is being joined to phppos_sales. What am I doing wrong?

You're believing more in yourself than you do in the MySQL RDBMS system.

IF an error message contradicts a cherished assumption, believe the message and check your assumptions.

This is a relatively wordy query, so the chances of making an error are not zero. Check your code more carefully and you'll find what MySQL is complaining about.

Isn't this the problem?

phppos_sales_items 
INNER JOIN phppos_sales USING (sale_id)) 
INNER JOIN phppos_items USING (item_id))

I don't think this should happen because I am joining phppos_sales_items to phppos_items, but it seems the query thinks it is being joined to phppos_sales.

I'm confused by this statement, because it implies that phppos_sales isn't involved, yet this snippet clearly shows that it is.