Mysql – Is it possible to change between SELECT column based on an “IF” statement? and use INNER JOIN variables

MySQLmysql-5.7performancequery-performance

I have a table of 'mapped_products':

product_id | price

A table of 'product_id_to_name':

product_id | product_name

(please don't ask me why, this is an already built web-app i am working on).

A table of 'non_mapped_products':

product_id | product_name

and a table of 'orders_history':

prodeuct_id | order_date

My task was to get a list of 'product_names' that were previously ordered (by checking in the 'orders_hisoty' table).
This is the query for this situation:

SELECT `non_mapped_products`.`product_name` 
FROM `non_mapped_products`
WHERE EXISTS ( SELECT 1  FROM `orders_history` WHERE `orders_history`.`product_id` = `non_mapped_products`.`product_id`)

Now I noticed that I have the same products in my 'non_mapped_pruducts' within my 'mapped_products' table – same 'product_id', different 'product_name':
I want to use the "product_name" if exist on my 'mapped_products' table instead of the one in tne 'non_mapped_products' table – if they do not exist I want to use the current 'product_name' from within the 'non_mapped_products'

I'm not so sure how to write this kind of query, it seems like an if/case statement under the select with an inner join (?)

SELECT (IF 'mapped_products'.'product_id' USE 'product_id_to_name'.'product_name' ELSE `non_mapped_products`.`product_name` ) 
FROM `non_mapped_products`
WHERE EXISTS ( SELECT 1  FROM `orders_history` WHERE `orders_history`.`product_id` = `non_mapped_products`.`product_id`)

***** It's important not to use the orders_history table (it's a huge table, and indexed badly).

Using MySQL database.

SELECT

    CASE
        WHEN mapped_products.product_id = non_mapped_products.product_id
        THEN product_id_to_name.product_name
    ELSE
        non_mapped_products.product_name
    END
        AS product_name

FROM non_mapped_products

INNER JOIN mapped_products
    ON mapped_products.product_id = non_mapped_products.product_id
INNER JOIN product_id_to_name
    ON product_id_to_name.product_id = mapped_products.product_id

WHERE EXISTS ( SELECT 1  FROM orders_history WHERE orders_history.product_id = non_mapped_products.product_id)

returns :
duplicate product names only from the mapped_products table

Best Answer

SELECT (IF 'mapped_products'.'product_id' USE 'product_id_to_name'.'product_name' ELSE `non_mapped_products`.`product_name` ) 
FROM `non_mapped_products`
WHERE EXISTS ( SELECT 1  FROM `orders_history` WHERE `orders_history`.`product_id` = `non_mapped_products`.`product_id`)

can be re-written to:

SELECT COALESCE( product_id_to_name.product_name,  non_mapped_products.product_name ) 
FROM non_mapped_products
    WHERE EXISTS ( SELECT 1  
                   FROM orders_history 
                   WHERE orders_history.product_id = non_mapped_products.product_id)
LEFT JOIN product_id_to_name ON  
    product_id_to_name.product_id = orders_history.product_id
Related Question