Combine information from sales flat order and sales flat order item sql Magento

Combine information from sales flat order and sales flat order item sql Magento

September 25, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Combine information from sales flat order and sales flat order item sql Magento

Combining Order Data in Magento: A Comprehensive Guide

As an experienced developer, I often receive queries about combining data from multiple tables in Magento, specifically for sales and order information. In this post, we’ll explore how to create a single SQL query that merges relevant data from the sales_flat_order, sales_flat_order_item, and sales_flat_invoice tables.

Understanding the Tables

Before diving into the query, let’s briefly review each of these tables:

  1. sales_flat_order: This table stores basic order information such as order status, number (increment ID), created_at timestamp, etc.
SELECT entity_id AS order_id,
       increment_id AS order_number,
       status AS order_status,
       created_at AS order_created
FROM sales_flat_order;
  1. sales_flat_order_item: This table contains details about each item in an order, including product name, type, price, etc.
SELECT item_id,
       order_id,
       name AS product_name,
       product_type,
       manufacturer,
       price
FROM sales_flat_order_item;
  1. sales_flat_invoice: This table holds invoice information related to the orders.
SELECT entity_id AS invoice_id,
       order_id,
       grand_total AS total_amount
FROM sales_flat_invoice;

Creating the Combined Query

To merge these tables, we’ll use SQL JOINs and SELECT statements. Here’s a query that combines the required data:

SELECT o.entity_id AS order_id,
       o.increment_id AS order_number,
       o.status AS order_status,
       o.created_at AS order_created,
       i.name AS product_name,
       i.product_type,
       i.manufacturer,
       i.price,
       inv.grand_total AS invoice_amount
FROM sales_flat_order o
LEFT JOIN sales_flat_order_item i ON o.entity_id = i.order_id
LEFT JOIN (SELECT order_id, grand_total FROM sales_flat_invoice) inv ON o.entity_id = inv.order_id;

Breaking Down the Query

Let’s break down this query:

  • We start with the sales_flat_order table (o), selecting the desired columns.
  • We use a LEFT JOIN to connect this table with sales_flat_order_item (i) on the entity_id (order ID) column, allowing us to fetch product details for each order.
  • Within a subquery, we select relevant invoice information from sales_flat_invoice, joining it back to the main query using another LEFT JOIN.
  • The result is a single table containing all the requested columns: Order Status, Order Number (Increment ID), Product Name, Product Type, Manufacturer, Price, and Miscellaneous Invoice Information.

Conclusion

Combining data from multiple tables in Magento can be achieved through careful use of SQL JOINs and subqueries. This approach ensures that you obtain a comprehensive view of your sales and order information without the need for complex programming logic.

Remember to test your queries thoroughly on a development or staging environment before implementing them in production, as they can significantly impact performance if not optimized properly.

For more advanced techniques in Magento database management and optimization, I recommend exploring our guide at Laravel Company.

Happy coding!

Catalog Database Orders Pricing

Our network