I've been trying without success to obtain the line level information about a specific order but i am getting the header information.

Take a look at this picture:


This is my query:

select o.increment_id, o.status, oi.price_incl_tax, t.carrier_code, o.created_at, oi.price_pvp, oi.created_at, o.shipping_instruction, o.payment_term
from sales_flat_order o
join sales_flat_order_item oi 
   on o.entity_id = oi.order_id 
left join sales_flat_shipment_track t 
   on o.entity_id = t.order_id 
where oi.parent_item_id is null

What's wrong? Can you please help me to solve this?


This is what i found in MYSQL:


The problem here is that i do not understand how should i look into this in order to obtain what i need :/.

  • shipping_instruction, payment_term are not fields of standard Magento in the sales_flat_order table. Therefore you should look into your customization if you would expect data in that fields - the SQL returns NULL because there is no data in that fields for the orders you have selected. Regarding the carrier_code: The tracking data is usually assigned to an order when something has been shipped, which might not be the case in your status waiting_approval (which again is not standard Magento but some customization). IMHO the SQL is correct and returns the data you have in database.
    – HelgeB
    Apr 1 '19 at 7:17
  • @HelgeB Thank you for replying, dude. I've edited my question. Have you got any idea about it? What should i do? Apr 1 '19 at 12:52
  • You have the data of order lines ( sales_flat_order_item table) in your SQL joined to the order head (sales_flat_order table) data. For example the SKU comes from there. So I don't really understand what your problem is - it's not clear what you are looking for and what is not working. Again, your SQL is correct and returns oder lines joined to order head data.
    – HelgeB
    Apr 1 '19 at 13:05
  • @HelgeB I think my query it is supposed to show the line information for every order, right? Everytime that i execute it, it shows NULL in some fields:, i.e: shipping_information, payment methods and so on. Why? A guy told me that if it is returning NULL its because i am returning the HEADER information,NOT the LINE information :/ Apr 2 '19 at 14:47
  • 1
    I can just repeat again what I have said already: you have order line and order head information joined. And your shipping_information and payment_methods (what ever that fields should be, they are not standard Magento) are order head information because you take them from sales_flat_order table. I can't tell you what's wrong with your custom data I just can say you can't get more data than you have in the database with SQL :-)
    – HelgeB
    Apr 2 '19 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.