sql - mysql conditional join depends on a column

Question

I'm trying to implement a generic notification system.. I have a data structure like this;

notification_base:id,type,object
notificiation_sub:id,user_id,not_base_id,lastNotifyTime
notification_action:id,user_id,not_base_id,action,creationDate

So basic scenario is, user create a notification base item by post a status, or upload a photo and etc.(?n this case status,photo refers to type field in notification_base table and object_id is post_id or photo_id depends on type) Then user subscribe for this notification_base item..(user 3 subscribe notification_base 5 and last notify time x)

After that another user touch this notification_base item. (for example commenting a status or like a photo) This action is recorded in notification_action table (user 5 make action 'like' on 12/02/2011)..

What I want is fetch the notification_base items from user subscription if last notifiytime smaller than a notification action then join them with notification_action.. I can succeed it with this sql ;

For user id 3;

select * from notification_action
                inner join notification_base on notification_action.not_base_id = notification_base.id
                inner join notification_sub on notification_action.not_base_id = notification_sub.not_base_id
                where notification_sub.user_id = 3 and notification_sub.lastShowDate < notification_action.creationDate ;

result is almost what i want, for example

user x did 'action' on your object which has 'type' and object_id at time t

but I also want to join on object_id depends on type.. So I can actually learn which object touched.. But as you can see type is dynamic, if type = post object id refers post_id on post table, if type=photo object id refers photo_id on photo table and etc..

I try to do it something like this but got some syntax error;

SELECT *
FROM notification_action
INNER JOIN notification_base
    ON notification_action.not_base_id = notification_base.id
INNER JOIN notification_sub
    ON notification_action.not_base_id = notification_sub.not_base_id CASE notification_base.type
            WHEN 'photo'
                THEN (
                        INNER JOIN photo
                            ON photo.id = notification_base.object_id
                        )
            ELSE (
                    INNER JOIN post
                        ON post.id = notification_base.object_id
                    )
            END
WHERE notification_sub.user_id = 3
    AND notification_sub.lastShowDate < notification_action.creationDate;

I know that it's not correct, it's like pseudo code

Answer 1

you can't conditionally create joins across tables. The best way to do is to use LEFT JOIN and use you CASE in your SELECT statement. You must have specify common columns you want to show,

SELECT *,
        (CASE notification_base.type
            WHEN 'photo'
            THEN photo.columnName1
            ELSE post.ColumnName1
        END) as ColumnName1,
        (CASE notification_base.type
            WHEN 'photo'
            THEN photo.columnName2
            ELSE post.ColumnName2
        END) as ColumnName2
FROM notification_action
    INNER JOIN notification_base
        ON notification_action.not_base_id = notification_base.id
    INNER JOIN notification_sub
        ON notification_action.not_base_id = notification_sub.not_base_id
    INNER JOIN photo
        ON photo.id = notification_base.object_id
    INNER JOIN post
        ON post.id = notification_base.object_id
WHERE notification_sub.user_id = 3
    AND notification_sub.lastShowDate < notification_action.creationDate;