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`)

I'll be editing my post and add a few of false examples which do not work that i tried.

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

2 Answers

1
TFBW On

If you LEFT JOIN your two product name tables to the order history table (or any other table which provides product_id), you will have access to both possible names. You can then use the COALESCE() function to obtain the first non-null value from the two name columns. You might want to create a VIEW of this to make life more convenient in other queries.

0
ThomasHaz On

This should work. It's a bit long winded, but simple enough to read through:

TL;DR:

SELECT orders_history.product_id, product_id_to_name.product_name
FROM orders_history 
JOIN product_id_to_name ON orders_history.product_id = product_id_to_name.product_id 
UNION SELECT orders_history.product_id, non_mapped_products.product_name
FROM orders_history JOIN non_mapped_products ON orders_history.product_id = non_mapped_products.product_id
WHERE non_mapped_products.product_id NOT IN (SELECT product_id FROM product_id_to_name WHERE 1)

Going through the statement:

SELECT orders_history.product_id, product_id_to_name.product_name
FROM orders_history 
JOIN product_id_to_name ON orders_history.product_id = product_id_to_name.product_id

This will return all the product_id_to_name.product_name values (your preferred default name as per the OP) where there an associated order.

The second part:

UNION SELECT orders_history.product_id, non_mapped_products.product_name
FROM orders_history JOIN non_mapped_products ON orders_history.product_id = non_mapped_products.product_id
WHERE non_mapped_products.product_id 
NOT IN (SELECT product_id FROM product_id_to_name WHERE 1)

This combines the result of the previous statement with selecting the non_mapped_products.product_name where the non_mapped_product.product_id isn't present in product_id_to_name.product_id.

The net result is all the order_history.product_id fields along with the product_name chosen preferably from product_id_to_name but with a fallback to the value from non_mapped_products.