Unknown column in field list when use join MySQL

I have 2 tables like this:

product_master:

|product_code|product_name|product_id|
|    code 1  |    P1      |  SP1     |
|    code 2  |    P2      |  SP2     |

campaign_product:

|campaign_id |campaign_code|product_code|product_id|
|    1       |    C1       |  code 1    |   SP1    |
|    2       |    C2       |  code 1    |   SP1    |
|    3       |    C3       |  code 2    |   SP2    |

This tables link with foreign key product_id

I want to get all product data with campaign_code column. I don’t want to get 2 campaign_product row with same product_id. So I use this query:

SELECT dmspro_mys_product_master.*, dmspro_mys_campaign_product.campaign_code
FROM dmspro_mys_product_master
INNER JOIN (SELECT DISTINCT dmspro_mys_campaign_product.product_id FROM dmspro_mys_campaign_product) AS cp
ON cp.product_id = dmspro_mys_product_master.product_id

It got error when I run:

Error in query (1054): Unknown column ‘dmspro_mys_campaign_product.campaign_code’ in ‘field list’

How I can fix this?

Thank you!

Answer

Column campaign_code is not returned by the subquery. Also, table alias dmspro_mys_campaign_product is not available in the outer query, only in the subquery.

You should modify the subquery so it returns it:

SELECT m.*, cp.campaign_code
FROM dmspro_mys_product_master m
INNER JOIN (
    SELECT DISTINCT product_id, campaign_code
    FROM dmspro_mys_campaign_product
) AS cp
ON cp.product_id = m.product_id

I also fixed your query to use table aliases for both tables: this shortens the query and makes it more readable.