MYSQL JOIN查询通知 - MYSQL JOIN Query Notifications

MYSQL JOIN Query Notifications

本文关键字：通知查询 JOIN MYSQL | 更新日期: 2023-09-27

我有四个表commettable、posttable、usertable和notifications表。我想建立一个通知系统。通知表具有以下

column id,
notifier,
notifying,
date,
type,
typeid

类型可以是1或2。如果是1，那就是评论；如果是2，那就是帖子。

我想构建一个MySQL查询，它可以获取用户的所有通知，并根据类型将它们与可注释的、可发布的连接起来。例如，如果类型为1，类型id为300，则它将从commmentable中提取注释列；如果类型为2，则它从post表中提取post列。

帖子中的列如下：

postid, post and commenttable commenter, comment, commentid

我已经构建了一个如下的查询，但它不像我希望的那样工作

SELECT 
    notificationstable.who,
    notificationstable.type,
    notificationstable.timestamp,
    notificationstable.date,
    commenttable.comment,
    commenttable.commentid,
    usertable.username,
    usertable.avatar,
    usertable.userid,
    usertable.verified,
    posttable.photo,
    posttable.title,
    posttable.postid
from
    notificationstable
        inner join
    usertable
        inner join
    posttable
        inner join
    commenttable ON notificationstable.who = usertable.userid 
 and posttable.postid = notificationstable.type 
 and commenttable.commentid = notificationstable.type
where
    notificationstable.whom = '$userid'
order by notificationstable.date desc

$userid是一个php变量

考虑到通知似乎不能同时应用于可发布和可评论的，您认为您的意思是进行外部联接

像这样的

SELECT 
    notificationstable.who,
    notificationstable.type,
    notificationstable.timestamp,
    notificationstable.date,
    commenttable.comment,
    commenttable.commentid,
    usertable.username,
    usertable.avatar,
    usertable.userid,
    usertable.verified,
    posttable.photo,
    posttable.title,
    posttable.postid
FROM notificationstable
INNER JOIN usertable
ON notificationstable.who = usertable.userid
LEFT OUTER JOIN posttable
ON posttable.postid = notificationstable.type
LEFT OUTER JOIN commenttable 
ON commenttable.commentid = notificationstable.type
WHERE notificationstable.whom = '$userid'
ORDER BY notificationstable.date DESC