CODEWITHSUNDEEP
mysqljoinconditional-statements
rashcroft
rashcroft

Reputation:

conditional join in mysql

I have a table id1, id2, type. type is an enumerated value containing a name of another table. I'd like to preform a join with the name of the table of type. For example:

switch($type)
case 'table1':
   join table1;
   break;
case 'table2':
   join table2;
   break;

How can I achieve this?

Upvotes: 28

Views: 45920

Answers (4)

toraman
toraman

Reputation: 604

I needed to implement such a thing with Laravel Query Builder. I was preparing a library and shouldn't rebuild the whole query, I wanted to utilize Eloquent as much as possible so I could only add a join to the query. This could be a little closer to what you want but also a lot uglier than you would expect:

SELECT
    `comments`.*,
    `commentable`.`created_at` AS `commentable_created_at`
FROM
    `comments`
LEFT JOIN ((
    SELECT
        *,
        CONCAT('post_', `id`) AS `morphed_key`
    FROM
        `posts`)
UNION (
SELECT
    *,
    CONCAT('image_', `id`) AS `morphed_key`
FROM
    `images`)) AS `commentable` ON
    CONCAT(`comments`.`commentable_type`, '_', `comments`.`commentable_id`)= `commentable`.`morphed_key`

The point of using this way is that you are now able to add WHERE clauses like WHERE commentable.owner_id=?

Upvotes: 0

Ilan
Ilan

Reputation: 71

In addition to previous answer: You can combine the two left joins results by using IF statement:

IF(t.type= 't2', t2.id, t3.id) as type_id

You can see another mysql conditional joins example at mysqldiary.com

Upvotes: 7

Greg
Greg

Reputation: 321578

You can't do it directly like that... you can do something like this though (not very pretty...):

SELECT
t.id,
t.type,
t2.id AS id2,
t3.id AS id3

FROM t
LEFT JOIN t2 ON t2.id = t.id AND t.type = 't2'
LEFT JOIN t3 ON t3.id = t.id AND t.type = 't3'

Upvotes: 27

Steve B.
Steve B.

Reputation: 57274

ugly way:

Table Types, T1, T2:

SELECT ... FROM Types, T1 , where Types.ID=T1.Types_ID AND Types.TYPE='TABLE1'
UNION
SELECT ... FROM Types, T2 , where Types.ID=T2.Types_ID AND Types.TYPE='TABLE2'

Upvotes: 9

Related Questions