How can I use a mySQL SELECT IF statement row to JOIN another table?

Question

I am using an IF statement in my mySQL SELECT statement and depending on the returned result from the IF statement I would like to join another table.

Eg. SELECT name, IF(apple = 'brown', color1, color2) AS ripeness FROM apples JOIN apple_type ON apple_type.color = ripeness

My problem is that I am receiving the error msg: Unknown column 'ripeness' in 'on clause'. Does anyone know how I can get around this to join the other table based on the result from the IF/ELSE in the Select clause?

Answer 1

You will have to duplicate the IF call there. For example:

SELECT
    name,
    IF(apple = 'brown', color1, color2) AS ripeness
FROM
    apples
    JOIN apple_type ON apple_type.color = IF(apple = 'brown', color1, color2)