CODEWITHSUNDEEP
mysqlselectnestedparentchildren
ElFietsbel
ElFietsbel

Reputation: 65

MySQL select inside select inside select

What i want to have are all parentid numbers that are children from multiple other parents which are also children from one other parent. All rows are inside the same table.

Now what i have so far is this (and it works):

SELECT * 
FROM location 
WHERE parentid IN (
    SELECT id 
    FROM location 
    WHERE parentid IN (
        SELECT id 
        FROM location 
        WHERE parentid = 0
    )
)

The only thing i'm wondering about is, would there be any better/faster way to achieve the exact same thing? The table consists of about 200.000 rows.

Upvotes: 1

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If I understand correctly, you want the grandchildren of parentid = 0. Personally, I think explicit joins are easier to follow and likely to produce a better execution plan:

select l2.*
from location l join
     location l1
     on l1.parentid = l.parentid join
     location l2
     on l2.parentid = l1.id
where l.parentid = 0;

The only caveat: Perhaps the where statement should be where l.id = 0. In this case you can actually simplify the query to:

select l2.*
from location l1
     location l2
     on l2.parentid = l1.id
where l1.parentid = 0;

Upvotes: 1

Related Questions