根据列值连接不同的表 [英] Joining different tables based on column value

问题描述

我有一个名为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;

每个notification都与一个不同的表相关，parent_type字段的值指定了我要用于* join该表的表的名称.所有目标表都有一些相似的列:

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;

当前，我正在使用此查询来选择通知，这些通知是它们在目标表中的相关行已存在，并且它们的is_visible字段为1:

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

但是由于它是LEFT JOIN，如果它与任何表都不匹配，它将返回通知，我该如何重写它，以便它不返回与目标表中任何行都不匹配的通知?我也尝试了CASE语句失败.

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.

推荐答案

我不是100％肯定语法正确，我现在没有机会对其进行测试，但是思路应该很清楚.

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

这篇关于根据列值连接不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！