1

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

0
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
  • And shouldn't I use inner joins (or in general "join"s) for the other (not orders_history & non_mapped_products) tables? – wellhellothere May 6 at 5:39
  • If all table have an index on product_id than this should work also. when having 'a lot of' records than using join instead of where exists should get better performance, but you will have to test how much performance increase you will get. – Luuk May 6 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.