复杂的SQL查询，检查多个表中的列值 [英] Complex SQL query, checking column values in multiple tables

问题描述

我有一个非常大的SQL查询来检查通知，并且我在表IE中有几种不同类型的通知:帖子，喜欢，评论，photoComments，photoLikes，videoLikes等(总是添加更多内容)我遇到了一个问题，我不确定如何最好地做到这一点.到目前为止，我的工作方式非常完美，并且确实很容易添加，但是，此通知类型不仅要检查另一个表，还要检查另外两个表，而我却无法检查使它正常工作.

I have a pretty huge SQL query to check for notifications, and I have several different types of notifications in the table, IE: posts, likes, comments, photoComments, photoLikes, videoLikes, etc. (Always adding more to it) And I have come into a problem, I'm not really sure how to best do this anymore. Thus far the way I have done it is working perfectly, and really quite easy to add to, however this one notification Type I have to check more than just one other table, I have to check two others and I haven't been able to get it to work.

所以这里是:(这只是我庞大的查询的一部分，实际上是唯一相关的部分)

So here it is: (This is only one part of my huge query, the only relevant part really)

n.uniqueID = ANY (
              SELECT photos.id
              FROM photos INNER JOIN posts ON (photos.id=posts.post)
              WHERE photos.state=0
              AND posts.state=0 
              AND posts.id = ANY (
                                   SELECT likes.postID FROM likes
                                   INNER JOIN posts ON (posts.id=likes.postID)
                                   WHERE likes.state=0 AND posts.state=0
                                  )
                )

因此，基本上我真正需要做的就是检查每个表中的state列，因为这表明是否将其删除(如果不为0，则将其删除并且不应返回) 就像这样:

So basically all I really need to do is check the state columns in each table because that says whether or not it is deleted or not (if it's not 0 then it's deleted and shouldn't be returned) So it would be like:

IF photos.state=0 AND posts.state=0 AND likes.state=0返回它.

• n.uniqueID，posts.post和photo.id都将相同 值.
• posts.id和likes.postID的值也将相同.

• n.uniqueID, posts.post, and photo.id will all be the same value.
• posts.id and likes.postID will also be the same value.

我的问题是，我认为它似乎并没有检查likes.state.

My issue is that it doesn't seem to be checking the likes.state, I don't think.

推荐答案

我认为您只想在单个查询中将三个表连接在一起:

I think you just want to join the three tables together in a single query:

n.uniqueID = ANY (
              SELECT photos.id
              FROM photos INNER JOIN
                   posts
                   ON photos.id=posts.post inner join
                   likes
                   on posts.id = likes.postId
              WHERE photos.state=0 and
                    posts.state=0 and
                    likes.state = 0
                  )

当状态为0的点赞或发布时，您的逻辑是不返回.似乎所有 的点赞和发布的状态均为零.为此，请使用having子句进行聚合:

Your logic is not to return when there is a like or post with the state of 0. It seems to be that all the likes and posts have a state of zero. For this, do an aggregation with a having clause:

n.uniqueID = ANY (
              SELECT photos.id
              FROM photos INNER JOIN
                   posts
                   ON photos.id=posts.post inner join
                   likes
                   on posts.id = likes.postId
              where photos.state = 0
              group by photos.id
              having MAX(posts.state) = 0 and MAX(likes.state) = 0

这篇关于复杂的SQL查询，检查多个表中的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！