In MySQL, how to switch a table to do an inner join ?

[deanstreet]

I have 3 hypothetical tables `greeting`, `english_greet`, `spanish_greet` as follows:

CREATE TABLE greeting (id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(20) NOT NULL,
language TINYINT(1) NOT NULL);

CREATE TABLE english_greet (greet VARCHAR(100) NOT NULL,
greet_id INT NOT NULL,
FOREIGN KEY (greet_id) REFERENCES greeting (id);

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.

 

[DaveV]

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;

 

[2rosy]

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