Search code examples
mysqlsqlsubquerycasewhere-clause

Query to get result from another id in one query

I have the following simple table

product_id    vendor_id    product_gtin    product_parent_id
646           1            123456789       0
4502          2            202020255       646

As a result I would like to get the product gtin for the id 4502 from the product_parent

Result example:

product_id    vendor_id    product_gtin    product_parent_id
646           1            123456789       0
4502          2            123456789       646

.

SELECT product_id, vendor_id, case when vendor_id !=1 then (select product_gtin from `products` where product_id = product_parent_id) else product_gtin end as gtin, product_parent_id FROM `products` where product_id in (646, 4502)

My problem is that while I run the query it does not know which id I refer to. So I get NULL as result for product_gtin for id 4502.

Any suggestions how to solve it would be appreciated

Solution

  • You can use table aliases:

    select product_id, vendor_id, 
    case when vendor_id <> 1 
        then (select product_gtin from `products` p1 where p1.product_id = p.product_parent_id) 
        else product_gtin 
    end as gtin, 
    product_parent_id 
from `products` p
where product_id in (646, 4502)