CODEWITHSUNDEEP
mysql
Amay Diam
Amay Diam

Reputation: 2601

Unknown column in on clause MySQL

I want to access a field in a subquery in MySQL, but I am getting an Unknown column 'var_customer_id' in 'on clause' error. This is my code:

SELECT *,
        CONCAT(customer_phone_1, ', ', customer_phone_2) AS customer_phone,
        CONCAT(customer_fax_1, ', ', customer_fax_2) AS customer_fax,
        CONCAT(customer_email_1, ', ', customer_email_2) AS customer_email,
        tbl_customer.customer_id AS var_customer_id,                
        (
            SELECT
                GROUP_CONCAT(tbl_service.service_name SEPARATOR ', ')
            FROM
                kuesioner_bbia.tbl_customer_service
                INNER JOIN kuesioner_bbia.tbl_service 
                ON (tbl_customer_service.service_id = tbl_service.service_id )
                AND tbl_customer_service.customer_id=var_customer_id                    
            GROUP BY tbl_customer_service.customer_id                   
            )               

        FROM tbl_customer
        ORDER BY customer_id ASC

So how to fix it? Sorry for my English.

Upvotes: 1

Views: 140

Answers (1)

Mark
Mark

Reputation: 8431

The error lies here:

ON (tbl_customer_service.service_id = tbl_service.service_id )
                AND tbl_customer_service.customer_id=var_customer_id

It can be either the following (note:I replaced the var_customer_id with tbl_customer.customer_id):

ON (tbl_customer_service.service_id = tbl_service.service_id )
WHERE tbl_customer_service.customer_id = tbl_customer.customer_id

OR(relocate the closing parenthesis):

ON (tbl_customer_service.service_id = tbl_service.service_id 
AND tbl_customer_service.customer_id = tbl_customer.customer_id)

Upvotes: 2

Related Questions