根据主表中列的值连接表 [英] Join table depending on value of column in main table

问题描述

notificationTable
ID              type        typeID      userID
==============================================
1               comment     34          2
2               accept      22          2

eventTable
ID              event       content
===================================
21              post        34  
22              accept      22

commentTable
ID              comment     eventID
===================================
34              'test'      21

我想根据列类型的值连接不同的表.这是我当前的伪代码:

I want to join different tables depending on the value of the column type. This is my current pseudocode:

SELECT *
FROM notificationTable notification
IF (notification.type == 'comment')
    LEFT JOIN commentTable comment
    ON notification.typeID = comment.ID
ELSEIF (notification.type == 'accept')
    LEFT JOIN evenTable event
    ON notification.typeID = event.ID
WHERE notification.userID = 2

有人知道我的意图的真正查询吗?

Anyone knows the real query for my intention?

推荐答案

类似

SELECT *
FROM notificationTable notification   
LEFT JOIN commentTable comment
ON (notification.typeID = comment.ID AND notification.type == 'comment')
LEFT JOIN evenTable event
ON (notification.typeID = event.ID AND notification.type == 'accept')
WHERE notification.userID = 2

但我强烈建议重新设计你的系统以避免多态关系...

But I strongly recommend to redesign your system to avoid polymorphic relationship...

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