Ok then.
Although I was the one to suggest subqueries to you, my choice for what you already are doing would be:
code:
SELECT Child.* FROM M1P AS Child
JOIN M1P AS Parent ON Child.M1PNON = Parent.M1ON
WHERE Parent.M1PNON LIKE '573.%'
This is a self join -- where you join the same table to itself -- and as such, you have to alias your table names. Although most people use very short aliases, I chose descriptive longer ones here, to illustrate what's going on.
This does exactly what your query does, but is a lot more readable. It is also very easy to have it dig one level deeper: you just add another self join:
code:
SELECT Child.* FROM M1P AS Child
JOIN M1P AS Parent1 ON Child.M1PNON = Parent1.M1ON
JOIN M1P AS Parent2 ON Parent1.M1PNON = Parent2.M1ON
WHERE Parent2.M1PNON LIKE '573.%'
This would get you the next level of child rows. If you want both levels returned in the same resultset, just UNION ALL the two together:
code:
SELECT Child.* FROM M1P AS Child
JOIN M1P AS Parent ON Child.M1PNON = Parent.M1ON
WHERE Parent.M1PNON LIKE '573.%'
UNION ALL SELECT Child.* FROM M1P AS Child
JOIN M1P AS Parent1 ON Child.M1PNON = Parent1.M1ON
JOIN M1P AS Parent2 ON Parent1.M1PNON = Parent2.M1ON
WHERE Parent2.M1PNON LIKE '573.%'
Hope that helps.
