CASE + IF MysQL query

Question

Problem is as follows. I have a product that can be in one of three categories (defined by category_id). Each category table has category_id field related to category_id in product table. So I have 3 cases. I'm checking If my product.category_id is in table one. If yes, I take some values. If not I check in tables that are left. What can I write In the ELSE section? Can anyone correct my query ?

CASE
    WHEN IF EXISTS(SELECT * FROM table1 WHERE category_id='category_id') THEN SELECT type_id FROM table1 WHERE category_id='category_id';
    WHEN IF EXISTS(SELECT * FROM table2 WHERE category_id='category_id') THEN SELECT value_id FROM table2 WHERE category_id='category_id';
    WHEN IF EXISTS(SELECT * FROM table3 WHERE category_id='category_id') THEN SELECT group_id FROM table3 WHERE category_id='category_id';
ELSE "dont know what here";

END;

Answer 1

You can possibly include this... SELECT "Unknown type" FROM table1;

Answer 2

You do not need to use ELSE if there is nothing left to do.

Answer 3

or something like this

CASE
    WHEN IF EXISTS(SELECT * FROM table1 WHERE category_id='category_id') THEN SELECT type_id FROM table1 WHERE category_id='category_id';
    WHEN IF EXISTS(SELECT * FROM table2 WHERE category_id='category_id') THEN SELECT value_id FROM table2 WHERE category_id='category_id';
ELSE SELECT group_id FROM table3 WHERE category_id='category_id';

Answer 4

In the else you would put whatever you want as default value, for example null.

I think that it would be much more efficient to make three left joins instead of several subqueries for each product in the result, and use coalesce to get the first existing value. Example:

select coalesce(t1.type_id, t2.value_id, t3.group_id)
from product p
left join table1 t1 on t1.category_id = p.category_id
left join table2 t2 on t2.category_id = p.category_id
left join table3 t3 on t3.category_id = p.category_id

Answer 5

In addition to Guffa's answer here is another approach - assuming @category_id is

SET @category_id = 'some_category_id_value'

then

SELECT t1.type_id
WHERE category_id = @category_id
UNION ALL
SELECT t2.value_id
WHERE category_id = @category_id
UNION ALL
SELECT t3.group_id
WHERE category_id = @category_id

should return what you ask for (and performance is not bad either).

If you have certain category_id in more then one table you will get multiple records (you can get out of that by limiting the number of results to 1; you might need to make it the whole union a subquery and order it, but not sure, consult the docs)

However, your question looks like you have a problem with a design of your tables

• why do you keep three category tables and not one?
• what is the relationship between type_id, value_id and group_id and why does it make sense to select them as if they were the same thing (what is the meaning/semantics of each table/column)?
• how do you guarantee that you don't have entries in multiple tables that correspond to one product (and implement other business rules that you might have)?

These questions could have valid answers, but you should know them :)