How to export users emails, first names and country using the backoffice sql manager ?

[USB83]

Hello,

 

I'm not good with sql so i need help exporting my customer's first names, email address, their country and if possible last times they had access to the store.

Using this query :

SELECT firstname, lastname, email, active FROM  psdb_customer

I managed to get email and first name but not the country and last access

From what i understood, since the other data is stored in a different db table, i should use a join to get data from both tables but i haven't been able to figure out how

Any help is welcome

Edited July 28, 2018 by USB83
to clarify (see edit history)

[Rolige]

Hello,

This query should work, just remember that to get a country of a customer they should have at least one address registered.

SELECT a.`firstname`,
       a.`lastname`,
       a.`email`,

  (SELECT c.`date_add`
   FROM `ps_guest` g
   LEFT JOIN `ps_connections` c ON c.`id_guest` = g.`id_guest`
   WHERE g.`id_customer` = a.`id_customer`
   ORDER BY c.`date_add` DESC LIMIT 1) AS `last_activity`,

  (SELECT cl.`name`
   FROM `ps_address` ad
   LEFT JOIN `ps_country_lang` cl ON cl.`id_country` = ad.`id_country`
   WHERE ad.`id_customer` = a.`id_customer`
   ORDER BY ad.`id_address` DESC LIMIT 1) AS `country_name`

FROM `ps_customer` a

 

Regards!