Add group_name of clients in SQL query

[Pau111111]

Hello Prestashoppers,

 

I'm trying to add the group name of each costumer in a SQL query:

 

SELECT a.`id_customer`, `firstname`, `lastname`, `email`, a.`active` AS `active`, `newsletter`, `optin` , a.date_add, gl.name as title, ( SELECT SUM(total_paid_real / conversion_rate) FROM ps_orders o WHERE o.id_customer = a.id_customer AND o.id_shop IN (1) AND o.valid = 1 ) as total_spent, ( 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 connect FROM `ps_customer` a LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1) WHERE 1 AND a.`deleted` = 0 ORDER BY `date_add` DESC

 

I've tried several things but it does not work for me, does anyone know what the correct syntax is?

[JeredBolton]

SELECT a.`id_customer`, `firstname`, `lastname`, `email`, a.`active` AS `active`, `newsletter`, `optin` , a.date_add, gl.name as title, gr.name, ( SELECT SUM(total_paid_real / conversion_rate) FROM ps_orders o WHERE o.id_customer = a.id_customer AND o.id_shop IN (1) AND o.valid = 1 ) as total_spent, ( 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 connect FROM `ps_customer` a LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1)
LEFT JOIN ps_group_lang gr ON a.id_default_group=gr.id_group AND gr.id_lang=a.id_lang
WHERE 1 AND a.`deleted` = 0 ORDER BY `date_add` DESC

[Pau111111]

SELECT a.`id_customer`, `firstname`, `lastname`, `email`, a.`active` AS `active`, `newsletter`, `optin` , a.date_add, gl.name as title, gr.name, ( SELECT SUM(total_paid_real / conversion_rate) FROM ps_orders o WHERE o.id_customer = a.id_customer AND o.id_shop IN (1) AND o.valid = 1 ) as total_spent, ( 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 connect FROM `ps_customer` a LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1)

LEFT JOIN ps_group_lang gr ON a.id_default_group=gr.id_group AND gr.id_lang=a.id_lang

WHERE 1 AND a.`deleted` = 0 ORDER BY `date_add` DESC

 

JeredBolton, thanks for your help, I'm trying to add DNI, address, and province name in this sql query:

 

I'm starting with DNI but doesn't works

SELECT a.`id_customer`, dni.dni,`firstname`, `lastname`,`company`,`email`, a.`active` AS `active`,`newsletter`, `optin` , a.date_add, gl.name as title, gr.name AS grupo, ( SELECT SUM(total_paid_real / conversion_rate) FROM ps_orders o WHERE o.id_customer = a.id_customer AND o.id_shop IN (1) AND o.valid = 1 ) as total_spent, ( 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 connect FROM `ps_customer` a LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1) LEFT JOIN ps_group_lang gr ON a.id_default_group=gr.id_group AND gr.id_lang=a.id_lang LEFT JOIN ps_address dni ON a.id_customer=dni.id_customer

WHERE 1 AND a.`deleted` = 0 ORDER BY `date_add` DESC

 

Thanks for your help!

[JeredBolton]

The error is pretty clear as to what the problem is:

 

Column 'firstname' in field list is ambiguous

 

You need to be more specific and specifiy which table you want the firstname (and lastname & company) from as these columns are in the address and customer tables:

 

 

SELECT a.`id_customer`, dni.dni, a.`firstname`, a.`lastname`, a.`compa...

[Pau111111]

The error is pretty clear as to what the problem is:

 

Column 'firstname' in field list is ambiguous

 

You need to be more specific and specifiy which table you want the firstname (and lastname & company) from as these columns are in the address and customer tables:

 

 

SELECT a.`id_customer`, dni.dni, a.`firstname`, a.`lastname`, a.`compa...

 

Thanks to you I have been able to do the other querys. I have noticed that when adding data from other tables as they have more than one address at most, the rows are duplicated with the client fields and with the different addresses. Is this the only way to show the different directions? Could it be done that the data would not be doubled or triplicated and the different addresses shown in columns within the same row?

I ask it in case it is too difficult to do or if it is possible.

 

example of my actual query:

 

SELECT a.`id_customer`, addr.dni, a.`firstname`, a.`lastname`,a.`company`,a.`email`, addr.phone, addr.alias, addr.`address1`, addr.`address2`, addr.city, st.name AS province, addr.postcode,  a.`active` AS `active`,a.`newsletter`,a. `optin` , a.date_add, gl.name as title, gr.name AS grupo, ( SELECT SUM(total_paid_real / conversion_rate) FROM ps_orders o WHERE o.id_customer = a.id_customer AND o.id_shop IN (1) AND o.valid = 1 ) as total_spent, ( 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 connect FROM `ps_customer` a LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1) LEFT JOIN ps_group_lang gr ON a.id_default_group=gr.id_group AND gr.id_lang=a.id_lang LEFT JOIN ps_address addr ON a.id_customer=addr.id_customer LEFT JOIN ps_state st ON addr.id_country=st.id_country AND addr.id_state=st.id_state

WHERE 1 AND a.`deleted` = 0 ORDER BY `date_add` DESC

 

Thank you very much @JeredBolton.