1

I need a SQL statement for the following

Email | user name | surname | addresses | password hash | subscription flag

Raw SQL to export

  • What do you expect to get in addresses? – Alex Paliarush Nov 20 '15 at 20:41
  • Shipping and Billing : Firstname, Lastname, Street, City, State, Postcode would be fine – brentwpeterson Nov 20 '15 at 20:45
  • Is it acceptable to get 2 rows for single customer if he has 2 addresses? In each row address will be unique, all other information duplicated. – Alex Paliarush Nov 20 '15 at 21:32
  • yes, this is fine – brentwpeterson Nov 20 '15 at 21:48
2

Here is your SQL (the first part loads users with addresses, second without addresses):

select customer_entity.email, first_name.value as first_name, last_name.value as last_name,

  CONCAT_WS(', ',
       NULLIF(address_first_name.value, ''),
       NULLIF(address_last_name.value, ''),
       NULLIF(address_street.value, ''),
       NULLIF(address_city.value, ''),
       NULLIF(address_postcode.value, ''),
       NULLIF(address_region.value, '')
  ) AS addresses,

  password_hash.value as password_hash,
  IFNULL(newsletter_subscriber.subscriber_status, 0) as subscription_flag

from customer_entity

join customer_entity_varchar as first_name on customer_entity.entity_id = first_name.entity_id
join customer_entity_varchar as last_name on customer_entity.entity_id = last_name.entity_id
join customer_entity_varchar as password_hash on customer_entity.entity_id = password_hash.entity_id

left join newsletter_subscriber on customer_entity.entity_id = newsletter_subscriber.customer_id

join customer_address_entity as address_address on customer_entity.entity_id = address_address.parent_id
join customer_address_entity_varchar as address_first_name on address_address.entity_id = address_first_name.entity_id
join customer_address_entity_varchar as address_last_name on address_address.entity_id = address_last_name.entity_id
join customer_address_entity_text as address_street on address_address.entity_id = address_street.entity_id
join customer_address_entity_varchar as address_city on address_address.entity_id = address_city.entity_id
join customer_address_entity_varchar as address_postcode on address_address.entity_id = address_postcode.entity_id
join customer_address_entity_varchar as address_region on address_address.entity_id = address_region.entity_id

where first_name.entity_type_id = 1 AND first_name.attribute_id = 5
AND last_name.entity_type_id = 1 AND last_name.attribute_id = 7
AND password_hash.entity_type_id = 1 AND password_hash.attribute_id = 12

AND address_first_name.entity_type_id = 2 AND address_first_name.attribute_id = 20
AND address_last_name.entity_type_id = 2 AND address_last_name.attribute_id = 22
AND address_street.entity_type_id = 2 AND address_street.attribute_id = 25
AND address_city.entity_type_id = 2 AND address_city.attribute_id = 26
AND address_postcode.entity_type_id = 2 AND address_postcode.attribute_id = 30
AND address_region.entity_type_id = 2 AND address_region.attribute_id = 28


UNION


select customer_entity.email, first_name.value as first_name, last_name.value as last_name, '' AS addresses,
password_hash.value as password_hash,
IFNULL(newsletter_subscriber.subscriber_status, 0) as subscription_flag

from customer_entity

join customer_entity_varchar as first_name on customer_entity.entity_id = first_name.entity_id
join customer_entity_varchar as last_name on customer_entity.entity_id = last_name.entity_id
join customer_entity_varchar as password_hash on customer_entity.entity_id = password_hash.entity_id

left join newsletter_subscriber on customer_entity.entity_id = newsletter_subscriber.customer_id

left join customer_address_entity as address_address on customer_entity.entity_id = address_address.parent_id

where first_name.entity_type_id = 1 AND first_name.attribute_id = 5
AND last_name.entity_type_id = 1 AND last_name.attribute_id = 7
AND password_hash.entity_type_id = 1 AND password_hash.attribute_id = 12

AND address_address.parent_id IS NULL
| improve this answer | |

Your Answer

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

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