Mysql query with conditional join

Question

I'm stuck on a MySQL query. I have a temporary orders table with the following structure:

session_id
product_id
product_type -- 'institute','state','region','country' 

For all institutes, states, regions and countries I have individual tables.

I want to create a MySQL query which fetches the data from my temp table and makes the join with the corresponding table depending upon product_type field.

If I use left join with 5 tables or use union it could be a really time consuming task; so I was looking for something different.

Answer 1

I would advise checking the answers in this question as they seem to match your specific problem https://stackoverflow.com/a/9327678/1213554

The short version though is that in order to be able to efficiently perform this request a database restructuring may well be required I'm afraid.

What you are looking for specifically is not possible, you'll have to use a UNION to do something along the lines of the following. As you say it will be a time consuming task though.

(
    SELECT tempData.*
    FROM tempData
    INNER JOIN institutes 
    ON institutes.id = tempData.product_id
    WHERE tempData.product_type = 'institute'
) UNION (
    SELECT tempData.*
    FROM tempData
    INNER JOIN states 
    ON states.id = tempData.product_id
    WHERE tempData.product_type = 'state'
) UNION (
    SELECT tempData.*
    FROM tempData
    INNER JOIN regions
    ON regions.id = tempData.product_id
    WHERE tempData.product_type = 'region'
) UNION (
    SELECT tempData.*
    FROM tempData
    INNER JOIN countries
    ON countries.id = tempData.product_id
    WHERE tempData.product_type = 'country'
)