mysqlsubquery

need some explanation about MySQL where ... in ... subquery

tableofdata

Above is a given table of data and I need to get the result data like the one below.

result


SELECT ID
FROM
(
    SELECT ID, PARENT_ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IN (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID 
                        IN (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL))
) AS aa
ORDER BY 1;

I asked others to get the answer like the code above but I could not figure out the process of this code (subquery part). I figured out what was going on with the first subquery but from the second one, I was lost. Can someone please explain the detailed process?

Solution

  • Work from the inside out with subqueries.

    So the first one is

    SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL

    This returns 2 ID's (1 and 2)

    Now this will be run with the values 1 and 2 passed to the IN

    SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IN(1,2)

    So this returns 3 ID's (3,4 and 5)

    Now this query is run

    SELECT ID, PARENT_ID FROM ECOLI_DATA WHERE PARENT_ID IN(3,4,5)

    So this returns

    ID      PARENT_ID
6       4
7       3

    Now the outermost query is run on the previous resultset

    SELECT ID

    Resulting in

    ID
6
7

    In reality it would appear you could cut one query out of this nest and get the same result

    SELECT ID FROM ECOLI_DATA
    WHERE PARENT_ID IN 
        (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IN 
            (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL))
) AS aa
ORDER BY 1;