3表SELECT查询的正确语法 [英] Proper Syntax for 3 table SELECT query

问题描述

我有3张桌子：

• tblPosts


• tblComments


• tblUsers

我正在尝试获取帖子列表以及相关的评论。棘手的部分似乎是在获取帖子和评论以显示适当的作者（用户）。这是我得到的最接近的信息，但是帖子作者不正确。我将CFOutput分组为 pid，因此我只希望获得一次帖子。

I'm trying to get a listing of Posts along with associated Comments. The tricky part seems to be getting the Posts and Comments to show the proper author (User). This is the closest I get but then the Posts authors are incorrect. I'm grouping my CFOutput on "pid", so I only get each post one time as I would expect.

SELECT tblPosts.pid
       , tblPosts.title
       , tblPosts.description
       , tblPosts.price
       , tblPosts.datecreated AS pdate
       , tblPosts.image1
       , tblComments.comment
       , tblComments.datecreated AS cdate
       , tblUsers.fname
       , tblUsers.lname
FROM   tblPosts
          LEFT JOIN tblComments ON tblPosts.pid = tblComments.pid
          LEFT JOIN tblUsers ON tblComments.uid = tblUsers.uid

有什么想法吗？
谢谢！

Any thoughts? Thanks!

推荐答案

由于两个表均包含作者ID，因此必须两次加入tblUser：一次发布并发表评论。这意味着您必须使用表别名来区分两者。遵循这些原则，其中 pa 是 Post Author的别名， ca 是 Comment Author的别名。

Since both tables contain an author id, you must to JOIN to tblUser twice: once for posts and once for comments. That means you must use a table alias to differentiate between the two. Something along these lines, where pa is the alias for "Post Author" and ca the alias for "Comment Author".

SELECT p.pid
       , p.title  
       , ... 
       , pa.fname AS PostAuthorFirstName
       , pa.lname AS PostAuthorLastName
       , ca.fname AS CommentAuthorFirstName
       , ca.lname AS CommentAuthorLastName
FROM tblPosts p 
        LEFT JOIN tblUsers pa ON pa.uid = p.uid
        LEFT JOIN tblComments c ON p.pid = c.pid
        LEFT JOIN tblUsers ca ON ca.uid = c.uid

这篇关于3表SELECT查询的正确语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！