Joining different tables based on column value

Question

I have a table called notifications:

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; 

Each notification is related to a different table, the value of parent_type field specifies the name of the table that I want to * join the table with. All target tables have several similar columns:

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; 

Currently I'm using this query for selecting notifcations that their related row in the target table exists and their is_visible field is 1:

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 

But since it is a LEFT JOIN 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 CASE statement unsuccessfully.

Answer 1

I'm not 100% sure the syntax is right and I have no chance to test it right now, but the idea should be clear.

SELECT DISTINCT n.id  FROM notifications n  JOIN (      (SELECT b.id, 'book' AS type FROM books b WHERE b.is_visible = 1)   UNION      (SELECT i.id, 'interview' AS type FROM interviews i WHERE i.is_visible = 1) ) ids ON n.parent_id = ids.id AND n.parent_type = ids.type WHERE n.user_id = 1