I know there are two tables sales_flat_order and sales_flat_order_item

but the relevant fields in sales_flat_order are:

customer_id, base_total_paid etc but no relation to product

and in the other table

order_id, product_id, but no relation to customer.

So I cant see any common field to link them.

I am trying to export 2 tables so that I can create a new database from scratch not based in magento, and I need to know what each customer ordered.

Something like this:

select product_id, customer_id
from cp8_sales_flat_order
left join cp8_sales_flat_order_item
on sales_flat_order_item.order_id = sales_flat_oder.entity_id

I am getting an error that #1054 - Unknown column 'sales_flat_order_item.order_id' in 'on clause'

2 Answers 2


In sales_flat_order table entity_id is the primary key and you can use that to get all items in an order

Columns to look in your case

Table: sales_flat_order

| Field            |
| entity_id  (PK)  |
| customer_id      |
| base_total_paid  |

Table: sales_flat_order_item

| Field            |
| item_id  (PK)    |
| order_id (FK)    |
| product_id       |

so you need to filter orders with selected customer id from sales_flat_order then using entity_id you need join sales_order_item order_id.

  • a query like the one I have just written? I am getting an error.
    – Arminius
    May 25, 2017 at 9:27

You can directly get product details from order. First get order placed by customer and then from that order id you can get ordered products in magento.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.