Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
605 views
in Technique[技术] by (71.8m points)

Complex Nested SQL Query

UPDATE I will debug my question and re-post it with more elaboration! I apologize for the confusion!

UPDATE Everyone who commented or posted an answer i will link you to my re-posting, thank you for taking the time to check my question and comment/answer!

I will make my question simple and straight to the point; The block of code below generates a syntax error when i include the first three subqueries.

What am i doing wrong?

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 
                 child1_extras.child1_extrasID, 
                 child1_extras.child1_extrasContent
              FROM 
                 child1_extras
              WHERE 
                 child1_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 
                 child2_extras.child2_extrasID, 
                 child2_extras.child2_extrasContent
              FROM 
                 child2_extras
              WHERE 
                 child2_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 
                 child3_extras.child3_extrasID, 
                 child3_extras.child3_extrasContent 
              FROM 
                 ( SELECT 
                         comments.comment_id, 
                         comments.comment
                      FROM 
                         comments 
                      WHERE 
                         comments.child3_extrasID = child3_extras.child3_extrasID ) child3_extras
           JOIN child3 
              ON child3_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}

Many thanks in advance.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Problem is all over your syntax. I reformatted your query some, but don't know exactly what refers to what. As was stated in a comment, simplify your queries individually to find out if they work, THEN add to the next in the chain.

The biggest example to clarify is joining to other tables. Whenever joining, you do the query and assign it an alias name reference. THAT Alias is the basis to the rest of the query. Do not confuse an alias WIHTIN a sub-select to the outer query. Without doing your exact query, look into the following

SELECT 
      parent1.parent1_id, etc, etc, etc
   FROM 
      parent1
         LEFT JOIN 
         ( SELECT 
                 child1.child1_id, 
                 child1.child1_content
              FROM 
                 child1 
              WHERE 
                 child1.child1_id = parent1.child1_id ) CHILD1

The lower-case "child1" INNER query is NOT the same as the OUTER upper case "CHILD1" as sampled above here. What I THINK you are trying to get is more of the following. Also note I am applying ALIASES to help confirm the difference between the ACTUAL table name vs whatever COULD be subquery conditions.

SELECT 
      P1.parent1_id, 
      C1.child1_content,
      C1X.child1_extrasID, 
      C1X.child1_extrasContent
   FROM 
      parent1 P1
         LEFT JOIN child1 C1
            on P1.child1_id = C1.child1_id
         LEFT JOIN child1_extras C1X
            on P1.child1_id = C1X.child1_id

Notice each additional table is joined from a possible previous. I also try to always have me left-side table (first within the query) joined or left-joined to the second alias result key ID.

Try the above simplified sample query with a few fixed criteria just to see if it works. THEN apply your extra table joins, where, order by, offset, etc.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...