title: Joining different tables based on column value
body: <p>I have a table called <code>notifications</code>:</p>
  
  <pre><code>CREATE TABLE `notifications` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL,
    `type` varchar(20) NOT NULL DEFAULT '',
    `parent_id` int(11) DEFAULT NULL,
    `parent_type` varchar(15) DEFAULT NULL,
    `type_id` int(11) DEFAULT NULL,
    `etc` NULL
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Each <code>notification</code> is related to a different table, the value of <code>parent_type</code> field specifies the name of the table that I want to <code>* join</code> the table with. All target tables have several similar columns:</p>
  
  <pre><code>CREATE TABLE `tablename` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `is_visible` tinyint(1) NOT NULL,      
    `etc` NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Currently I'm using this query for selecting notifcations that their related row in the target table exists and their <code>is_visible</code> field is <code>1</code>:</p>
  
  <pre><code>SELECT n.id, 
  FROM notifications n 
  LEFT JOIN books b ON n.parent_id = b.id AND n.parent_type = 'book' AND b.is_visible = 1
  LEFT JOIN interviews i ON n.parent_id = i.id AND n.parent_type = 'interview' AND i.is_visible = 1
  LEFT JOIN other tables...
  WHERE n.user_id = 1
  GROUP BY n.id
  </code></pre>
  
  <p>But since it is a <code>LEFT JOIN</code> it returns the notification if it matches any table or not, how can I rewrite it so it doesn't return notifications that don't match with any row in the target table? I have also tried the <code>CASE</code> statement unsuccessfully.</p>