SQL query to get all addresses associated with customer in Magento

SQL query to get all addresses associated with customer in Magento

July 12, 2026 · Illustrated author avatarBy Stefan Bogdanescu
SQL query to get all addresses associated with customer in Magento

Retrieving Customer Addresses from Magento MySQL Database

As you’re migrating away from Magento, it’s essential to retrieve all customer addresses accurately for your new platform. In this post, I’ll guide you through crafting the correct SQL query to fetch all addresses associated with each customer in a Magento 1.6.x database.

Understanding Your Current Query

You’ve started by querying the customer_entity and customer_address_entity tables:

SELECT email, group_concat(a.entity_id)
FROM customer_entity as c
INNER JOIN customer_address_entity as a ON a.parent_id = c.entity_id
GROUP BY email;

This query successfully retrieves customer emails along with their corresponding address IDs. However, you’re uncertain if customer_address_entity.parent_id indeed represents the parent (customer) ID. Let’s verify this.

Confirming Customer ID in Parent Field

In Magento 1.x, the parent_id field in the customer_address_entity table is a foreign key that links to the customer’s primary key (entity_id) in the customer_entity table.

To confirm this, you can query the database schema:

DESCRIBE customer_address_entity;

The output should display ‘PARENT_ID’ as a field with REFERENCES customer_entity (entity_id).

Crafting the Complete Query

Now that we’ve verified the relationship, let’s enhance your query to include more relevant fields for each address:

SELECT
    c.entity_id AS customer_id,
    c.email,
    a.entity_id AS address_id,
    a.street,
    a.city,
    a.region,
    a.postcode,
    a.country_id
FROM
    customer_entity AS c
INNER JOIN
    customer_address_entity AS a ON a.parent_id = c.entity_id;

This query joins the customer_entity and customer_address_entity tables and selects essential fields for each address, including:

  • Customer ID: c.entity_id
  • Customer email: c.email
  • Address ID: a.entity_id
  • Street
  • City
  • Region (state)
  • Postcode (zip code)
  • Country ID

Pagination Consideration

If you’re dealing with a large number of customers and addresses, consider adding pagination to your query:

SELECT
    c.entity_id AS customer_id,
    c.email,
    a.entity_id AS address_id,
    a.street,
    a.city,
    a.region,
    a.postcode,
    a.country_id
FROM
    customer_entity AS c
INNER JOIN
    customer_address_entity AS a ON a.parent_id = c.entity_id
LIMIT 100 OFFSET 20;

Replace 100 and 20 with your desired page size and offset value, respectively.

Conclusion

In this post, we explored how to retrieve all addresses associated with each customer in a Magento database. By understanding the relationship between tables and crafting the appropriate SQL query, you can efficiently extract the necessary data for your migration.

Remember, always test your queries thoroughly before running them on your production environment.

If you’re looking for guided support or expert consultations during your migration process, I recommend reaching out to experts at LaravelCompany.

Happy migrating!

Magento 1 Database Email Customers

Our network