Jump to content

I am asking for help with sql query [solved]


franekfrk

Recommended Posts

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 by franekfrk (see edit history)
Link to comment
Share on other sites

, (
            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.

Link to comment
Share on other sites

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'

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...