Well this is kind of a follow-on from my other thread:
Number Range Query but it's a different problem now.
I am using the following SQL query:
code:
SELECT * M1PNON IN (SELECT M1ON FROM M1P WHERE M1PNON LIKE '573.%')
Firstly it finds the records where M1PNON has the order number of 573 and returns a list of the matching M1ON numbers.
Then it finds all the records that have their M1PNON field as one of the list just retireved.
This is working fine, but I have just found out that I need to do it another level deep as well, but I'm confused as to how. Anyone have any pointers?
I havent explained it very well, I'll post some sample data:
code:
M1P - M1PNON
1 - 573
2 - 573
3 - 1
4 - 1
5 - 2
6 - 5
7 - 5
records 1 and 2 are top level parents.
3 and 4 are child records of record 1 and 5 is a child of number 2.
The query i have above can currently retireve both the parents and the first children, but it won't return a childs child, such as 6 and 7 which are children of record 5.
So what do I need to add to the query to also make it retrieve the third level? Is it another IN statement? If so where exactly?
Thanks for any help
/ 
