查询链接4个表 [英] query linking 4 tables

问题描述

我目前有4张桌子:

用户:

• 用户ID (PK)
• FName
• SName
• Pic

帖子:

• ReportID (PK)
• 用户ID
• 标题
• 说明
• Pic

评论:

• CommentID (PK)
• 用户ID
• PostID

用户订阅:

• ID (PK)
• 用户ID
• PostID
• isRead

我需要检索用户订阅的所有帖子的所有评论详细信息(有评论可用).此查询用于填充应用程序中的我的活动供稿页面.

当用户发表帖子时，他们将订阅该帖子；当用户发表评论时，他们将订阅该帖子.因此，查询中WHERE的"AND"部分的原因是为了确保有人对用户订阅的帖子发表了评论.因此，如果还没有人对此发表评论，那么作者不应收到有关他们所写文章的回复.因此，想法是用户a将收到类似用户b在标题为xxx的帖子c上发表评论"的响应.

查询的问题是我没有从userSubscriptions表中收到正确的isRead Val.

我的尝试:

  $ ReportDetails_Query ="SELECT Posts.ReportID，Posts.Title AS postTitle，Posts.Pic为postPic，Posts.Description为postDescription，Posts.datePosted为postDatePosted，Posts.photoWidth为postPhotoWidth，posts.photoHeight为postPhotoHeight，Users.FName为authorFname，Users.SName作为authorSname，Users.Pic作为postAuthorPic，Users.UserID作为authorID，userSubscriptions.isRead来自用户订阅内联接在userSubscriptions.PostID上的帖子= Posts.ReportID内加入用户在Posts.UserID = Users.UserID在哪里Posts.ReportID(从UserSubscriptions中选择POSTID，其中用户ID =吗?)和(选择COUNT(CommentID)来自评论的用户ID<>?AND Comments.ReportID = Posts.ReportID)>0GROUP BY Posts.ReportIDLIMIT?，10; 

SQL Fiddle: http://sqlfiddle.com/#!9/9aa16

小提琴正在返回1行，但是我在phpmyadmin中的查询返回了两个正确的行，但是具有错误的isRead vals.

当传递userID 36时，我返回了两个正确的行，但是每一行中的isRead col是错误的，因此在加入userSubscriptions表时出了点问题

注意:当我在查询中包含userSubscriptions.ID时，可以看到我得到4和16，它们是不正确的行，并且它们的isRead vals为1.但是对于用户36的isRead vals，所有atm值均为0.

编辑:我已经修改了查询，它似乎正在工作.如果有人发现有问题，请指出.我在第一个内部联接中添加了和:

  SELECT Posts.ReportID，Posts.Title AS postTitle，Posts.Pic为postPic，Posts.Description为postDescription，Posts.datePosted为postDatePosted，Posts.photoWidth为postPhotoWidth，posts.photoHeight为postPhotoHeight，Users.FName为authorFname，Users.SName作为authorSname，Users.Pic作为postAuthorPic，Users.UserID作为authorID，userSubscriptions.isRead来自用户订阅内连接在userSubscriptions.PostID = Posts.ReportID和userSubscriptions.UserID = 36上的帖子内加入用户在Posts.UserID = Users.UserID在哪里Posts.ReportID(从UserSubscriptions中选择POSTID，其中用户ID =吗?)和(选择COUNT(CommentID)来自评论的用户ID<>?AND Comments.ReportID = Posts.ReportID)>0GROUP BY Posts.ReportIDLIMIT?，10 

解决方案

也许您混淆了外键.

  INNER JOIN userSubscriptions.PostID = Posts.ReportID上的帖子 

要使其正常工作，PostID必须是Posts.ReportID的外键

查看UserSubscriptions.PostID的FOREIGN KEY定义，然后找出它链接到的表.

I have 4 tables at present:

Users:

• UserID (PK)
• FName
• SName
• Pic

Posts:

• ReportID (PK)
• UserID
• Title
• Description
• Pic

Comments:

• CommentID (PK)
• UserID
• PostID

UserSubscriptions:

• ID (PK)
• UserID
• PostID
• isRead

I need to retrieve all comment details for all posts a user is subscribed to (where there is a comment available). This query is used to populate a my activity feed page in an app.

When a user makes a post, they are subscribed to that post and when a user comments on a post, they are subscribed to that post. So the reason for the 'AND' part of the WHERE in the query is to ensure that someone has commented on a post a user is subscribed to. So the author should not receive a response with a row about a post they have written if no one has commented on it yet. so the idea is that user a will receive a response like 'user b has commented on post c with title xxx'.

The issue with my query is that I am not receiving the correct isRead Val from the userSubscriptions table.

My attempt:

$ReportDetails_Query = "SELECT Posts.ReportID, Posts.Title AS postTitle, Posts.Pic as postPic, Posts.Description as postDescription,
            Posts.datePosted AS postDatePosted, Posts.photoWidth as postPhotoWidth, posts.photoHeight as postPhotoHeight, Users.FName as authorFname,
            Users.SName as authorSname, Users.Pic as postAuthorPic, Users.UserID As authorID, userSubscriptions.isRead
            FROM UserSubscriptions 
            INNER JOIN Posts ON userSubscriptions.PostID = Posts.ReportID
            INNER JOIN Users ON Posts.UserID = Users.UserID  
            WHERE Posts.ReportID IN 
                                (SELECT PostID FROM UserSubscriptions WHERE UserID = ?) 
            AND 
                (SELECT COUNT(CommentID) 
                FROM Comments WHERE UserID <> ? 
                AND Comments.ReportID = Posts.ReportID) > 0                         
            GROUP BY Posts.ReportID
            LIMIT ?,10";

SQL Fiddle: http://sqlfiddle.com/#!9/9aa16

The fiddle is returning 1 row but my query here in phpmyadmin returns two correct rows but with incorrect isRead vals.

When passing in the userID 36, I am getting back the two correct rows but the isRead col in each row is wrong, so there is something going wrong in the join to the userSubscriptions table

Note: When I include userSubscriptions.ID in the query, I can see I am getting 4 and 16 which are incorrect rows and they have isRead vals of 1. But the isRead vals for user 36 are 0 for all posts atm.

EDIT: I've modified the query and it seems to be working. If anyone sees an issue with it, please point it out. i've added an and into the first inner join:

SELECT Posts.ReportID, Posts.Title AS postTitle, Posts.Pic as postPic, Posts.Description as postDescription,
            Posts.datePosted AS postDatePosted, Posts.photoWidth as postPhotoWidth, posts.photoHeight as postPhotoHeight, Users.FName as authorFname,
            Users.SName as authorSname, Users.Pic as postAuthorPic, Users.UserID As authorID, userSubscriptions.isRead
            FROM UserSubscriptions 
            INNER JOIN Posts ON userSubscriptions.PostID = Posts.ReportID and userSubscriptions.UserID = 36
            INNER JOIN Users ON Posts.UserID = Users.UserID  
            WHERE Posts.ReportID IN 
                                (SELECT PostID FROM UserSubscriptions WHERE UserID = ?) 
            AND 
                (SELECT COUNT(CommentID) 
                FROM Comments WHERE UserID <> ? 
                AND Comments.ReportID = Posts.ReportID) > 0                             
            GROUP BY Posts.ReportID
            LIMIT ?,10

解决方案

Perhaps you have mixed up foreign keys.

INNER JOIN Posts ON userSubscriptions.PostID = Posts.ReportID

For this to work, PostID has to be a foreign key into Posts.ReportID

Look at the FOREIGN KEY definitions for UserSubscriptions.PostID, and find out which table it's linked to.

这篇关于查询链接4个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！