Complicated conditional SQL query

Question

I'm not even sure if it's possible but I need it for my Access database. So I have following db structure :

Now I need to perform a query that takes category_id from my product and do the magic :
- let's say product belongs to console (category_id is in table Console)
- from console_types take type_id, where category_id == category_id
- but if product belongs to console_game (category_id is in table console_game)
- from console_game take game_cat_id, where category_id == category_id

I'm not sure if mysql is capable of such thing. If not I'm really f&%ranked up. Maybe there is a way to split this into 2,3 separate queries ?

EDIT :

I've found something like "IF EXISTS (SELECT type_id FROM console WHERE category_id='category_id') ". Maybe this will be helpful ?

Answer 1

You need to use the case statement to do a select like:

Select Case (expression)
Case option1
    statement1
Case option2
    statement2

Example Access case statement

Case statements work in MySql, Sql Server etc. (Syntax varies between implementations).

Answer 2

You can solve this by doing three sub queries and use UNION ALL to merge them.