Shoutbox

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   

Do you only want to include one more level (easy), or did you just use that for illustration and really want to recursively follow the parent-child relationship chain and get all child records there are, arbitrary number of levels deep (hard)?

Also, I am uncertain why you are using LIKE instead of =, if your data resembles the example.  Is there any reason for that?

Yea its just one more level, i thought there was only ever going to be two, but I just found out its actually three.

Ah yea i used a bad example, I have to use LIKE as the actual data is 573.001, 573.002 etc etc

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. 

Thanks, that sounds like that would do exactly what I need, except I'm not sure if I can use it.

I am using these SQL queries in a data transfer program that connects to an IBM server, and I don't full full SQL access, just some fields to fill in:

If I tick 'Enable group functions' then the 'Group By' and 'Having' fields become enable, but the 'Join By' is always disabled for some reason.

So using what you told me, I'm not sure if I can do it that way, any ideas?

EDIT:  I managed to do it another way, prob not very efficient or whatever, but it works .

code:

---

M1PNON IN (SELECT M1ON FROM M1P WHERE M1PNON IN (SELECT M1ON FROM M1P WHERE M1PNON LIKE '573.%'))

---

Thanks for the help

Whatever works is good (and I hate these query setup dialogs)!

What does the "Details..." button do?  It could be many things, but I wouldn't be surprised if it let you edit SQL directly somewhere under there.  May be worth finding out, in case you need to use more advanced queries in your project in the future.

Other than that, the "Join by" box might become enabled if you specify columns from more than one table, or select more than one table somewhere before this dialog. Which might make self-joins a challenge, depending on how aware the dialog is of table aliasing...