SQL statement for Customer email and address

Question

I need a SQL statement for the following

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

Raw SQL to export

Answer 1

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