适当的JOIN用于向查询添加第四个表 [英] Proper JOIN for Adding a 4th Table to a Query

问题描述

提出了类似的问题

  SELECT p.pid 
，p.title 
，c。 cid 
，c.comment 
，c.statusType 
，COALESCE（cnt.HasClaim，0）AS hasClaim 
，pa.fname AS PFName 
，pa.lname AS PLName 
，ca.fname AS CFName 
，ca.lname AS CLName 
 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 
 LEFT JOIN（
 SELECT pid，COUNT（*）AS hasClaim 
 FROM tblComments 
 WHERE statusType = 1 
 GROUP BY pid 
）cnt ON cnt.pid = p.pid 
 ORDER BY p.pid，c.cid 
  

Asked a similar question HERE but this extends it.

Here's the original query:

SELECT  p.pid
    , p.title
    , p.description
    , p.price
    , p.datecreated AS pdate
    , p.image1
    , c.cid
    , c.comment
    , c.datecreated AS cdate
    , pa.fname AS PFName
    , pa.lname AS PLName
    , ca.fname AS CFName
    , ca.lname AS CLName
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
ORDER BY p.pid

I need to add a fourth table (tblPostStatus) that holds the status of each post(statusType) which can be one of 2 different values. When I try to add the JOIN I seem to get duped rows, one for each row in tblPostStatus (there are 3 records in this table). This table has fields sid, pid, uid, statusType.

New Query:

    SELECT  p.pid
    , p.title
    , p.description
    , p.price
    , p.datecreated AS pdate
    , p.image1
    , c.cid
    , c.comment
    , c.datecreated AS cdate
    , pa.fname AS PFName
    , pa.lname AS PLName
    , ca.fname AS CFName
    , ca.lname AS CLName
    , ps.statusType
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
LEFT JOIN tblPostStatus ps ON p.pid = ps.pid
ORDER BY p.pid

See query result pics:

Do these results look proper or am I doing something incorrectly?

解决方案

(Summary from chat)

The overall requirements changed a bit. Ultimately "Status" is related to a post, user and comment record, so status was moved to the comment table. An additional requirement was added: identify posts with "claim" comments.

SQL Fiddle

SELECT  p.pid
    , p.title
    , c.cid
    , c.comment
    , c.statusType 
    , COALESCE(cnt.HasClaim, 0) AS HasClaim
    , pa.fname AS PFName
    , pa.lname AS PLName
    , ca.fname AS CFName
    , ca.lname AS CLName
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
      LEFT JOIN (
          SELECT pid, COUNT(*) AS HasClaim
          FROM  tblComments 
          WHERE statusType = 1
          GROUP BY pid
        ) cnt ON cnt.pid = p.pid
ORDER BY p.pid, c.cid

这篇关于适当的JOIN用于向查询添加第四个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！