使用严格的WHERE子句从四个表中获取数据 - getting data from four tables with tight WHERE clause

getting data from four tables with tight WHERE clause

本文关键字：四个数据获取 WHERE 子句 | 更新日期: 2023-09-27

我需要帮助从不同的表中获取数据并插入到其他不同的表中。

"SELECT commentID, date, comment, subject, parentID, aBUserID FROM comments WHERE status = 'APPROVED'"
"SELECT topicID, subForumID, aBUserID, lastPostID, views, replies, startDate FROM topic WHERE status = 'APPROVED' AND topicID = $parentid";
 // $parentID need to be matched from above query parentID,
"SELECT userName FROM users WHERE aBUserID = $cmtaBUserID";
// $cmtaBUserID = aBUserID from first query
"SELECT userName FROM users WHERE aBUserID = $topicaBUserID";
//$topicaBUserID = aBUserID from second query

最后两个查询来自同一个表，但使用不同的where子句我使用不同的内连接左连接从解决方案张贴在这里，但这些工作都没有为我卡住，因为过去2周请帮助

PS数据从上述所有查询将被插入到一个表，我需要这些组合，所以我可以有他们都在一个地方

如果要在同一查询中执行操作，请使用'OR'

"SELECT userName FROM users WHERE aBUserID = $cmtaBUserID OR aBUserID = $topicaBUserID";

请尝试一下

SELECT userName from users where aBUserID IN(SELECT aBUserID from comments where status = 'APPROVED')

无法测试，但也许这就是你正在寻找的

SELECT c.commentID, c.date, c.comment, c.subject, c.parentID, c.aBUserID, 
       t.topicID, t.subForumID, t.aBUserID, t.lastPostID, t.views, t.replies, t.startDate,
       u.userName
FROM 
       comments c 
       left outer join topic t on t.topicID = c.parentID
       left outer join users u on u.aBUserID = c.aBUserID and u.aBUserID = t.aBUserID
WHERE
       c.status = 'APPROVED' and t.status = 'APPROVED';

try this:

SELECT
    comment.[commentID],
    comment.[date],
    comment.[comment],
    comment.[subject],
    comment.[parentID],
    comment.[aBUserID], 
    commentuser.[userName],
    topic.[topicID],
    topic.[subForumID],
    topic.[aBUserID],
    topic.[lastPostID],
    topic.[views],
    topic.[replies],
    topic.[startDate],
    topic.[userName]
FROM comments comment
LEFT OUTER JOIN users commentuser
    ON commentuser.aBUserID = comment.[aBUserID]
LEFT OUTER JOIN
(
    SELECT
        t.[topicID],
        t.[subForumID],
        t.[aBUserID],
        t.[lastPostID],
        t.[views],
        t.[replies],
        t.[startDate],
        u2.[userName] --user from users table joined to topics table
    FROM topic t
    LEFT OUTER JOIN users u
        ON u.aBUserID = t.[aBUserID]
    WHERE t.[status] = 'APPROVED'
) topic
    ON topic.topicID = comment.parentID
WHERE comment.[status] = 'APPROVED'