I am asking for help with sql query [solved]

[franekfrk]

Hello, I have such a sql query:

SELECT
								a.`id_customer`, `firstname`, `lastname`, `email`, `company`, 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  AND a.`active` = 1 
			
			 ORDER BY `connect` desc

I would like to add some more values like phone and phone_mobile taken from the table ps_address.

 

How do I do it? Please help

Edited March 5, 2018 by franekfrk (see edit history)

[musicmaster]

, (
            SELECT ad.phone FROM ps_address ad 
            WHERE ad.id_customer = a.id_customer
            LIMIT 1
        ) as phone

You might insert the above before FROM. For the mobile phone you would need yet another of such constructions.

There is a quicker way but that will only work on MariaDb and older versions of MySql.

[franekfrk]

SELECT
								a.`id_customer`, `firstname`, `lastname`, `email`, `phone`, `company`, 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
        , (
            SELECT ad.phone FROM ps_address ad 
            WHERE ad.id_customer = a.id_customer
            LIMIT 1
        ) as phone
			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  AND a.`active` = 1 
			
			 ORDER BY `connect` desc

This query make error: #1054 - Unknown column 'phone' in 'field list'

[musicmaster]

You shouldn't include it a second time. 

[kensterdotnet]

You have `phone` at the top and down at the bottom where Musicmaster told you to insert it.

Edited March 2, 2018 by kensterdotnet (see edit history)

[franekfrk]

Thx

@musicmaster and @kensterdotnet