CODEWITHSUNDEEP
mysqlsql
Nitin Pawar
Nitin Pawar

Reputation: 263

Mysql: use join table column value in where clause

catalog_product_entity table

enter image description here

catalog_product_relation table

enter image description here

catalog_product_entity_varchar table

enter image description here

I want to export the sku and url from above tables:

I tried to join this three tables and select the url values if catalog_product_entity tables entity_id matches with the child_id column in catalog_product_relation then use the parent_id in where clause other wise use entity_id; 

select  cpe.sku
,       value 
from    catalog_product_entity as cpe
left join 
        catalog_product_entity_varchar as cpev
on      cpe.entity_id = cpev.entity_id
where   cpev.attribute_id = 119 and 
        type_id = "simple" and 
        cpev.store_id=0 and 
        cpev.entity_id = 
        (
        select  parent_id
        from    catalog_product_relation 
        where   child_id = cpe.entity_id
        )

Above query is not the correct query i uploaded here for understanding

Edit:

catalog_product_entity_varchar taables header

enter image description here

Upvotes: 1

Views: 1410

Answers (2)

Andomar
Andomar

Reputation: 238086

You can join the value twice, once directly, and once through the intermediate parent-child table. Use coalesce to fall back to the child value when the parent value is not found:

select  cpe.sku
,       coalesce(parent_cpev.value, child_cpev.value)
from    catalog_product_entity as cpe
left join 
        catalog_product_entity_varchar as child_cpev
on      child_cpev.entity_id = cpe.entity_id
        and child_cpev.attribute_id = 119
        and child_cpev.store_id = 0
left join
        catalog_product_relation as cpr
on      cpe.entity_id = cpr.child_id
left join 
        catalog_product_entity_varchar as parent_cpev
on      parent_cpev.entity_id = cpr.parent_id
        and parent_cpev.attribute_id = 119
        and parent_cpev.store_id = 0
where   cpe.type_id = 'simple'

Upvotes: 1

Matteus Barbosa
Matteus Barbosa

Reputation: 2725

type_id column doesn't have (missing) table alias prefix specified in your query.

Upvotes: 0

Related Questions