If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help needed with SELECT query
Hi,
I need some help with constructing a SELECT query: WONUMBER PARENT AMOUNT HASCHILDREN =========================================== 1234 NULL 10 Y 2345 1234 20 Y 3456 1234 15 N 4567 2345 5 N As you can see, wonumber 1234 has 2 children (2345 and 3456). Wonumber 2345 is a child of wonumber 1234 but also has a child of it's own - 4567. SELECT WONUMBER FROM TABLE WHERE WONUMBER = '1234' OR PARENT = '1234' would return wonumber 1234, 2345 and 3456. But what if I want to find wonumber 1234's children and children's children? One further problem is that the table can contain up to 7 nested levels of parent/children relations. I.e. wonumber 1234 could have children, who have children, who have children... What I need is a select statement that would return all wonumbers that are related to the top level (where the PARENT field contains a null value) wonumber, regardless the number of levels. TIA Pete |
#2
|
|||
|
|||
Help needed with SELECT query
This is not a simple task in JET SQL.
One of the issues is that there is not an easy way to prevent a record being its own grandparent or great-grandparent. It that does occur, you have an infinite recursion, and so any query you do write cannot run to completion. Joe Celko has written some stuff on this. Links: http://www.intelligententerprise.com/001020/celko.shtml http://www.dbmsmag.com/9603d06.html http://www.dbmsmag.com/9604d06.html http://www.dbmsmag.com/9605d06.html http://www.dbmsmag.com/9606d06.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "PO" h wrote in message ... Hi, I need some help with constructing a SELECT query: WONUMBER PARENT AMOUNT HASCHILDREN =========================================== 1234 NULL 10 Y 2345 1234 20 Y 3456 1234 15 N 4567 2345 5 N As you can see, wonumber 1234 has 2 children (2345 and 3456). Wonumber 2345 is a child of wonumber 1234 but also has a child of it's own - 4567. SELECT WONUMBER FROM TABLE WHERE WONUMBER = '1234' OR PARENT = '1234' would return wonumber 1234, 2345 and 3456. But what if I want to find wonumber 1234's children and children's children? One further problem is that the table can contain up to 7 nested levels of parent/children relations. I.e. wonumber 1234 could have children, who have children, who have children... What I need is a select statement that would return all wonumbers that are related to the top level (where the PARENT field contains a null value) wonumber, regardless the number of levels. |
#3
|
|||
|
|||
Help needed with SELECT query
PERHAPS something like the following SQL statement
SELECT L1.WONumber , L2.WONumber , L3.WONumber , L4.WONumber , L5.WONumber , L6.WONumber , L7.WONumber FROM (((((YourTable as L1 LEFT JOIN YourTable as L2 ON L1.WONumber = L2.Parent) LEFT JOIN YourTable as L3 ON L2.WONumber = L3.Parent) LEFT JOIN YourTable as L4 ON L3.WONumber = L4.Parent) LEFT JOIN YourTable as L5 ON L4.WONumber = L5.Parent) LEFT JOIN YourTable as L6 ON L5.WONumber = L6.Parent) LEFT JOIN YourTable as L7 ON L6.WONumber = L7.Parent WHERE L1.Parent is Null John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County PO wrote: Hi, I need some help with constructing a SELECT query: WONUMBER PARENT AMOUNT HASCHILDREN =========================================== 1234 NULL 10 Y 2345 1234 20 Y 3456 1234 15 N 4567 2345 5 N As you can see, wonumber 1234 has 2 children (2345 and 3456). Wonumber 2345 is a child of wonumber 1234 but also has a child of it's own - 4567. SELECT WONUMBER FROM TABLE WHERE WONUMBER = '1234' OR PARENT = '1234' would return wonumber 1234, 2345 and 3456. But what if I want to find wonumber 1234's children and children's children? One further problem is that the table can contain up to 7 nested levels of parent/children relations. I.e. wonumber 1234 could have children, who have children, who have children... What I need is a select statement that would return all wonumbers that are related to the top level (where the PARENT field contains a null value) wonumber, regardless the number of levels. TIA Pete |
Thread Tools | |
Display Modes | |
|
|