Try this
SELECT `e`.*, `at_prefix`.`value` AS `prefix`, `at_firstname`.`value` AS `firstname`, `at_middlename`.`value` AS `middlename`, `at_lastname`.`value` AS `lastname`, `at_suffix`.`value` AS `suffix`, CONCAT(IF(at_prefix.value IS NOT NULL AND at_prefix.value != '', CONCAT(LTRIM(RTRIM(at_prefix.value)), ' '), ''), LTRIM(RTRIM(at_firstname.value)), ' ', IF(at_middlename.value IS NOT NULL AND at_middlename.value != '', CONCAT(LTRIM(RTRIM(at_middlename.value)), ' '), ''), LTRIM(RTRIM(at_lastname.value)), IF(at_suffix.value IS NOT NULL AND at_suffix.value != '', CONCAT(' ', LTRIM(RTRIM(at_suffix.value))), '')) AS `name`, `at_default_billing`.`value` AS `default_billing`, `at_billing_street`.`value` AS `billing_street`, `at_billing_postcode`.`value` AS `billing_postcode`, `at_billing_city`.`value` AS `billing_city`, `at_billing_telephone`.`value` AS `billing_telephone`, `at_billing_fax`.`value` AS `billing_fax`, `at_billing_region`.`value` AS `billing_region`, `at_billing_country_code`.`value` AS `billing_country_code`, `at_default_shipping`.`value` AS `default_shipping`, `at_shipping_street`.`value` AS `shipping_street`, `at_shipping_postcode`.`value` AS `shipping_postcode`, `at_shipping_city`.`value` AS `shipping_city`, `at_shipping_telephone`.`value` AS `shipping_telephone`, `at_shipping_fax`.`value` AS `shipping_fax`, `at_shipping_region`.`value` AS `shipping_region`, `at_shipping_country_code`.`value` AS `shipping_country_code`, `at_taxvat`.`value` AS `taxvat` FROM `customer_entity` AS `e` LEFT JOIN `customer_entity_varchar` AS `at_prefix` ON (`at_prefix`.`entity_id` = `e`.`entity_id`) AND (`at_prefix`.`attribute_id` = '826') LEFT JOIN `customer_entity_varchar` AS `at_firstname` ON (`at_firstname`.`entity_id` = `e`.`entity_id`) AND (`at_firstname`.`attribute_id` = '1') LEFT JOIN `customer_entity_varchar` AS `at_middlename` ON (`at_middlename`.`entity_id` = `e`.`entity_id`) AND (`at_middlename`.`attribute_id` = '827') LEFT JOIN `customer_entity_varchar` AS `at_lastname` ON (`at_lastname`.`entity_id` = `e`.`entity_id`) AND (`at_lastname`.`attribute_id` = '2') LEFT JOIN `customer_entity_varchar` AS `at_suffix` ON (`at_suffix`.`entity_id` = `e`.`entity_id`) AND (`at_suffix`.`attribute_id` = '828') LEFT JOIN `customer_entity_int` AS `at_default_billing` ON (`at_default_billing`.`entity_id` = `e`.`entity_id`) AND (`at_default_billing`.`attribute_id` = '7') LEFT JOIN `customer_address_entity_text` AS `at_billing_street` ON (`at_billing_street`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_street`.`attribute_id` = '16') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_postcode` ON (`at_billing_postcode`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_postcode`.`attribute_id` = '14') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_city` ON (`at_billing_city`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_city`.`attribute_id` = '15') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_telephone` ON (`at_billing_telephone`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_telephone`.`attribute_id` = '17') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_fax` ON (`at_billing_fax`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_fax`.`attribute_id` = '18') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_region` ON (`at_billing_region`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_region`.`attribute_id` = '12') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_country_code` ON (`at_billing_country_code`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_country_code`.`attribute_id` = '11') LEFT JOIN `customer_entity_int` AS `at_default_shipping` ON (`at_default_shipping`.`entity_id` = `e`.`entity_id`) AND (`at_default_shipping`.`attribute_id` = '8') LEFT JOIN `customer_address_entity_text` AS `at_shipping_street` ON (`at_shipping_street`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_street`.`attribute_id` = '16') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_postcode` ON (`at_shipping_postcode`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_postcode`.`attribute_id` = '14') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_city` ON (`at_shipping_city`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_city`.`attribute_id` = '15') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_telephone` ON (`at_shipping_telephone`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_telephone`.`attribute_id` = '17') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_fax` ON (`at_shipping_fax`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_fax`.`attribute_id` = '18') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_region` ON (`at_shipping_region`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_region`.`attribute_id` = '12') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_country_code` ON (`at_shipping_country_code`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_country_code`.`attribute_id` = '11') LEFT JOIN `customer_entity_varchar` AS `at_taxvat` ON (`at_taxvat`.`entity_id` = `e`.`entity_id`) AND (`at_taxvat`.`attribute_id` = '830') WHERE (`e`.`entity_type_id` = '1')
Basically this query you will get using customer collection with join of address data. if this doesn't work you can create one sample file put below code in that and run it in browser you will get the query which you can run directly in database.
<?php
require_once 'app/Mage.php';
umask(0);
$app = Mage::app('default');
$collection = Mage::getResourceModel('customer/customer_collection')
->addNameToSelect()
->addAttributeToSelect('email')
->addAttributeToSelect('created_at')
->addAttributeToSelect('group_id')
->joinAttribute('billing_street', 'customer_address/street', 'default_billing', null, 'left')
->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
->joinAttribute('billing_fax', 'customer_address/fax', 'default_billing', null, 'left')
->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
->joinAttribute('billing_country_code', 'customer_address/country_id', 'default_billing', null, 'left')
->joinAttribute('shipping_street', 'customer_address/street', 'default_shipping', null, 'left')
->joinAttribute('shipping_postcode', 'customer_address/postcode', 'default_shipping', null, 'left')
->joinAttribute('shipping_city', 'customer_address/city', 'default_shipping', null, 'left')
->joinAttribute('shipping_telephone', 'customer_address/telephone', 'default_shipping', null, 'left')
->joinAttribute('shipping_fax', 'customer_address/fax', 'default_shipping', null, 'left')
->joinAttribute('shipping_region', 'customer_address/region', 'default_shipping', null, 'left')
->joinAttribute('shipping_country_code', 'customer_address/country_id', 'default_shipping', null, 'left')
->joinAttribute('taxvat', 'customer/taxvat', 'entity_id', null, 'left');
echo $collection->getSelect();
Best Answer