JOINS大型查询中的SQL子查询链 [英] Chain of SQL subqueries within large query of JOINS

问题描述

我正在尝试使用复杂的嵌套选择操作来构造SQL查询！

I am trying to structure a SQL query with complex nested select operation!

我原来的SQL查询成功加入了大约30个表，可以按需检索数据！但是，在30个表中的每个获取的记录 在另一个名为(Comments)的表中有很多记录！我想做的是将(Comments表)中的每个记录分配给另一个记录中的记录 通过ID的30个表，并在一个查询中一起检索它们.但这不是唯一的挑战，除了表中的记录外，这30个表中的一些表还具有其他功能. (注释表)更多记录在另一个表中(称为"extras")，因此我正在寻找主子查询中的其他子查询 外部主要查询中的一个LEFT JOIN.

My original SQL query successfully JOINS around 30 tables, the data are retrieved as wanted! However every fetched record in the 30 tables has many records in another table called (Comments)! What I want to do is to atribute every record in the (Comments table) to its record in the other 30 tables by IDs and retrieve them all together in one query. Yet this is not the only challenge, some of the 30 tables have in addition to the records in (Comments table) more records in another table called (extras), so i am looking for additional subquery within the main subquery within a LEFT JOIN inside the outter main query.

使想法更清晰；没有子查询的脚本将如下所示:

To make the idea more clear; without subquery the script will be as following:

$query = $mysqli->query("
SELECT 
parent1.parent1_id,
parent1.child1_id,
parent1.child2_id,
parent1.child3_id,
parent2.parent2_id,
parent2.child1_id,
parent2.child2_id,
parent2.child3_id,
child1.child1_id, 
child1.child1_content,
child2.child2_id, 
child2.child2_content,  
child3.child3_id, 
child3.child3_content
    FROM 
        parent1
    LEFT JOIN child1
        ON child1.child1_id = parent1.child1_id

    LEFT JOIN child2
        ON child2.child2_id = parent1.child2_id

    LEFT JOIN child3
        ON child3.child3_id = parent1.child3_id

    LEFT JOIN followers
        ON parent1.user_id = followers.followed_id
        AND parent1.parent1_timestamp > followers.followed_timestamp
        AND parent1.parent1_id NOT IN (SELECT removed.isub_rmv FROM removed)
        AND parent1.parent1_hide = false
    WHERE 
        followers.follower_id = {$_SESSION['info']}
        {$portname_clause}
    ORDER BY 
        parent1.parent1_timestamp DESC
    LIMIT 
        {$postnumbers} 
    OFFSET 
        {$offset}
")

// Now fetching and looping through the retrieved data

while($row = $query->fetch_assoc()){

    echo $row['child1_content'];
    $subquery1 = $mysqli->query("SELECT extras.child1_id, 
    extras.extrasContent FROM extras WHERE extras.child1_id = 
   {$row['child1_id']}");
    while($row1 = $subquery1->fetch_assoc()){
        echo $row1['extrasContent'];
    }
    echo $row['child2_content'];
    $subquery2 = $mysqli->query("SELECT extras.child2_id, 
    extras.extrasContent FROM extras WHERE extras.child2_id = 
    {$row['child2_id']}");
    while($row2 = $subquery2->fetch_assoc()){
        echo $row2['extrasContent'];
     }

    echo $row['child3_content'];
    $subquery3 = $mysqli->query("SELECT extras.child3_id, 
    extras.extrasContent FROM extras WHERE extras.child3_id = 
    {$row['child3_id']}");
    while($row3 = $subquery3->fetch_assoc()){
        echo $row3['extrasContent'];

        // Here i need to run additional query inside the subquery 3 to retrieve the (Comments table) data beside (extras table)

        $subquery4 = $mysqli->query("SELECT comments.comment_id, comments.comment FROM comments WHERE comments.child3_id = {$row['child3_id']} OR comments.child3_id = {$row3['child3_id']}");
        while($row4 = $subquery4->fetch_assoc()){
        echo $row4['comment'];
        }

    }

} // No sane person would make such code

因为上面的代码完全是多余的，所以我寻找了一种更好的方法来执行它，这就是我遇到子查询的地方 概念，但我对子查询一无所知，研究完之后不久，我想到了这个凌乱的代码，请在下面检查！

Because the code above would be totally rediclious i searched for a better way to carry it out, and thats where i came across the subquery concept, but i do not know anything about subqueries, and shortly after i studied it i came up with this messy code, check it below!

我没有在这里发布原始代码，因为它太长了，我包含了我要应用的表格的虚拟示例 查询以演示该过程.

I am not posting the origianl code here because it is too long, i am including a virtual example of the tables i want to apply the query on in order to demonstrate the process.

SELECT 
parent1.parent1_id,
parent1.child1_id,
parent1.child2_id,
parent1.child3_id,
parent2.parent2_id,
parent2.child1_id,
parent2.child2_id,
parent2.child3_id
FROM 
    parent1
    LEFT JOIN 
        ( SELECT 
        child1.child1_id, 
        child1.child1_content
    FROM 
        child1 
    WHERE 
        child1.child1_id = parent1.child1_id ) child1
        ( SELECT extras.extrasID, extras.extrasContent
    FROM 
        extras
    WHERE 
        extras.child1_id = child1.child1_id )
        ON parent1.child1_id = child1.child1_id
    LEFT JOIN child2
        ( SELECT 
        child2.child2_id, 
        child2.child2_content
        FROM 
           child2 
        WHERE
            child2.child2_id = parent1.child2_id )
            ( SELECT 
            extras.extrasID, 
            extras.extrasContent
            FROM 
                extras
            WHERE 
                extras.child2_id = child2.child2_id )
                    ON parent1.child2_id = child2.child2_id
                LEFT JOIN child3
                    ( SELECT 
                    child3.child3_id, 
                    child3.child3_content
                FROM 
                    child3
                WHERE 
                    child3.child3_id = parent1.child3_id )
                    ( SELECT 
                        extras.extrasID, 
                        extras.extrasContent 
                    FROM 
                        ( SELECT 
                            comments.comment_id, 
                            comments.comment
                        FROM 
                            comments 
                        WHERE 
                            comments.child3_id = extras.child3_id ) extras
                        JOIN child3 
                             ON extras.child3_id = child3.child3_id )

          ON parent1.child3_id = child3.child3_id
   LEFT JOIN followers
        ON parent1.user_id = followers.followed_id
        AND parent1.parent1_timestamp > followers.follower_timestamp
        AND parent1.parent1_id NOT IN (SELECT removed.isub_rmv FROM removed)
        AND parent1.parent1_hide = false
   WHERE 
        followers.follower_id = {$_SESSION['info']}
        {$portname_clause}
   ORDER BY 
       parent1.parent1_timestamp DESC
   LIMIT 
       {$postnumbers} 
   OFFSET
       {$offset} // <-- Sorry for the bad code formatting!

我正在使用MySql 5.6.37

I am using MySql 5.6.37

我还没有掌握子查询的概念，坦率地说，我在学习它时迷失了自己，感到困惑，并且由于下面的注释中也提到了另一个原因.

I did not get the hang of the subquery concept yet, frankly i got lost and confused as i was studying it and for another reason too mentioned in the note below.

注意:我预先向您道歉，因为我住的地方没有电，ADSL或电话，而且我的住所可能没有得到即时答复 USB调制解调器几乎没有信号，我每天只有2个小时的时间由desil发生器产生3个小时的电.我充电 我的笔记本电脑和Internet上网，剩下的一两个小时用于处理其他生活用品. 我知道我正在开玩笑，因为我正在开发一个没有电或没有永久性互联网的Web项目.但是生活并不能提供一切！大声笑.

Note: I apologize in advance that i might not be on instant reply because where i live there is no electrecity or ADSL or phones and my USB modem hardly gets signal, i have only two hours of averege three hours a day of electericity generated by a desil generator. i recharge my laptop and check internet and the remaining one-two hours are for other life stuff. I know the joke is on me as i am developing a web project without electricity or permanent internet. BUT LIFE DOES NOT GIVE EVERYTHING! lol.

推荐答案

这就是我解决问题的方法！

This is how i solved the problem!

  SELECT 
        parent1.parent1_id,
        parent1.child1_id,
        child1.child1_id,
        child1.child1_content,
        comments.comment_id, 
        comments.comment, 
        comments.child1_id
  FROM parent1 LEFT JOIN 
(
    SELECT comments.comment_id, comments.comment, comments.child1_id
      FROM 
    (
        SELECT comments.comment_id,comments. comment, comments.child1_id
          FROM comments
    ) comments JOIN child1
        ON comments.child1_id = child1.child1_id
) comments
    ON child1.child1_id = comments.

它需要一些别名，然后就可以了.

It needs some aliases and then it's good to go.

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