Two join statements gives Unknown column

Question

I have this SQL query :

SELECT `main_table`.* FROM `ves_brand` AS `main_table`
INNER JOIN 
    (SELECT DISTINCT value from catalog_product_entity_int 
    where row_id in 
        (select row_id from catalog_product_entity_int 
        WHERE (attribute_id, value) IN ( (99, 4) ))) AS `t` 
ON main_table.brand_id = t.value 
INNER JOIN `catalog_product_entity` AS `cpeiz` 
ON cpeiz.row_id = catalog_product_entity_int.row_id 
WHERE (cpeiz.attribute_set_id != 19)

This gives the error: #1054 - Unknown column 'catalog_product_entity_int.row_id' in 'on clause'

Even if I set an alias for the column catalog_product_entity_int.row_id, the column is always "unknown".

Answer 1

catalog_product_entity_int is defined inside your subquery, you can't reference it directly from outside your subquery. (think of it like a local variable, you have to pass it back somehow to the main query)

Very dependent on your dataset, but something like this will work

SELECT `main_table`.* FROM `ves_brand` AS `main_table`
INNER JOIN 
    (SELECT DISTINCT value, row_id from catalog_product_entity_int 
    where row_id in 
        (select row_id from catalog_product_entity_int 
        WHERE (attribute_id, value) IN ( (99, 4) ))) AS `t` 
ON main_table.brand_id = t.value 
INNER JOIN `catalog_product_entity` AS `cpeiz` 
ON cpeiz.row_id = t.row_id 
WHERE (cpeiz.attribute_set_id != 19)