I am trying to structure a SQL query with complex nested select operation!
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!
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.
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.
