I have 3 hypothetical tables `greeting`, `english_greet`, `spanish_greet` as follows: Code: CREATE TABLE greeting (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, language TINYINT(1) NOT NULL); Code: CREATE TABLE english_greet (greet VARCHAR(100) NOT NULL, greet_id INT NOT NULL, FOREIGN KEY (greet_id) REFERENCES greeting (id); Code: CREATE TABLE spanish_greet (greet VARCHAR(100) NOT NULL, greet_id INT NOT NULL, FOREIGN KEY (greet_id) REFERENCES greeting (id); It is just that I am separating the actual contents from greeting table and putting that into either `english_greet` or `spanish_greet` table, flagged by the language column (say 1 for english_greet, 2 for spanish_greet). The question is how can I do a join *conditional* on language = 1 OR 2? I tried some variations of SELECT * from greeting CASE language WHEN 1 THEN INNER JOIN english_greet on greeting.id = english_greet.greet_id WHEN 2 THEN INNER JOIN spanish_greet on greeting.id = spanish_greet.greet_id, but showing syntax errors. I don't want to forcefully join all 3 tables, as each greeting is either in english or spanish but never both.
Try this: SELECT gr.* , if( gr.language = 1, english.greet, spanish.greet ) as `Greet` from greeting gr left join english_greet english on gr.greeting.id = english.greet_id left join spanish_greet spanish on gr.greeting.id = spanish.greet_id;
poor database design leads to messy queries. Short answer, you can't in regular sql but can by dynamically creating the sql given your constraints: inner join, not forcefully join all 3 tables