Mysql conditional join condition column dependent

Question

I am trying to implement a common notification system. I have a data structure similar to 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 

Thus, the main scenario is that the user creates a basic notification element by posting a status or uploading a photo, etc. (in this case, the status, the photo refers to the type field in the notification_base table, and object_id - post_id or photo_id depends on the type) Then the user subscribes to this notification_base element .. (user 3 subscribes notification_base 5 and the last notification time x)

After that, another user will touch this notification_base element. (for example, a status comment or photo) This action is recorded in the notification_action table (user 5 makes an action "like" 12/02/2011).

What I want is to get notification_base elements from a user subscription if the last unknown time is less than the notification action, and then attach them to the action_ notification. I can succeed 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

- this is almost what I want, for example

user x took an “action” on your object that has a “type” and object_id at time t

but I also want to join object_id, it depends on the type .. So I can really find out which object I touched .. But since you can see that the type is dynamic if type = post object id refers to the post_id on the column column, if type = photo object identifier refers to photo_id on the photo table, etc.

I am trying to do something similar, but received a 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 this is not correct, it looks like a pseudo code

Answer 1

You cannot conditionally create joins between tables. It is best to use LEFT JOIN and use CASE in your SELECT . You must specify the common columns that 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;