mysql JOIN ON IF() ??

Question

Hi there,

I am trying to use sql such as this:

SELECT t.*, t2.* FROM templates t
LEFT JOIN IF(t.t_type = 0,'templates_email',
IF(t.t_type = 1,'templates_sms','templates_fax')) t2
ON t.t_id = t2.t_id;

Is it possible to do something like that?

basically I want to join on one of three tables based on the value from the row.

Is this recommended if it is possible?

---

update
So,

basically the templates table is the table that contains all the information that every template must have, eg name, id, description

then you have the templates_x tables these tables contain the fields that are unique to each template type.
(There are quite a few and having a single table with null fields for those not applicable is not practical).

The tables are called templates_x however the appropriate x is stored in the templates table as an int flag.

The join between the templates_x tables and the templates table is through the t_id.

So what do you suggest?

Answer 1

Is it possible to do something like that?

No, you can't use dynamically assigned tables (join or otherwise) without using dynamic SQL syntax (See MySQL's PreparedStatement syntax).

This non-dynamic re-write of your pseudo-query assumes that the three template tables you join to all have the same number of columns, and the same data types:

SELECT t.*, 
       te.*
  FROM TEMPLATES t
  JOIN TEMPLATES_EMAIL te ON te.t_id = t.t_id
 WHERE t.t_type = 0
UNION
SELECT t.*, 
       ts.*
  FROM TEMPLATES t
  JOIN TEMPLATES_SMS ts ON ts.t_id = t.t_id
 WHERE t.t_type = 1
UNION
SELECT t.*, 
       tf.*
  FROM TEMPLATES t
  JOIN TEMPLATES_FAX tf ON tf.t_id = t.t_id
 WHERE t.t_type NOT IN (0, 1)

Answer 2

Instead of a conditional join, you can join the data then select which field you want.

Example:

SELECT 
  CASE 
    WHEN t.type = 0 THEN [templates_email]
    WHEN t.type = 1 THEN [templates_sms]
    WHEN t.type = 2 THEN [templates_fax]
  END AS [selected_field]
FROM 
  t
LEFT JOIN t1 ON t.t_id = t1.t_id
LEFT JOIN t2 ON t.t_id = t2.t_id
LEFT JOIN t3 ON t.t_id = t3.t_id

Answer 3

I'm afraid it would have to be something like this (if all tables have the same columns):

SELECT t.*, t2.* FROM templates t
LEFT JOIN templates_email t2
ON t.t_id = t2.t_id
WHERE t.t_type = 0
UNION
SELECT t.*, t2.* FROM templates t
LEFT JOIN templates_sms t2
ON t.t_id = t2.t_id
WHERE t.t_type = 1
UNION
SELECT t.*, t2.* FROM templates t
LEFT JOIN templates_fax t2
ON t.t_id = t2.t_id
WHERE t.t_type NOT IN (0,1)

Or like this:

SELECT t.*, t1.*, t2.*, t3.*
FROM templates t    
LEFT JOIN templates_email t1
ON t.t_id = t1.t_id
AND t.type_id = 0
LEFT JOIN templates_sms t2
ON t.t_id = t2.t_id
AND t.type_id = 1
LEFT JOIN templates_fax t3
ON t.t_id = t3.t_id
AND t.type_id NOT IN (0,1)

.. and live with all your NULL columns.