title: mysql conditional join depends on a column
body: <p>I'm trying to implement a generic notification system.. I have a data structure like this;</p>
  
  <pre><code>notification_base:id,type,object
  notificiation_sub:id,user_id,not_base_id,lastNotifyTime
  notification_action:id,user_id,not_base_id,action,creationDate
  </code></pre>
  
  <p>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)</p>
  
  <p>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)..</p>
  
  <p>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 ;</p>
  
  <p>For user id 3;</p>
  
  <pre><code>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 &lt; notification_action.creationDate ;
  </code></pre>
  
  <p>result is almost what i want, for example</p>
  
  <p>user x did 'action' on your object which has 'type' and object_id at time t</p>
  
  <p>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..</p>
  
  <p>I try to do it something like this but got some syntax error;</p>
  
  <pre><code>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 &lt; notification_action.creationDate;
  </code></pre>
  
  <p>I know that it's not correct, it's like pseudo code </p>