On 2012-03-01, at 9:20 PM, Jay Blanchard <jay.blanchard@xxxxxxxxxxxxxxxxxxx> wrote: >> [snip] >> Can you show the output of the function above? >> [/snip] > > 0 > SELECT DISTINCT `TIER1DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' > Executives and Management > > Normally this query alone returns 9 rows of data. Each of these rows should be included in the next query where TIER1DATA = each of the nine in succession > > 1 > SELECT DISTINCT `TIER2DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `TIER1DATA` = 'Executives and Management' > Executives and ManagementLeadership > 2 > SELECT DISTINCT `TIER3DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `TIER2DATA` = 'Executives and ManagementLeadership' > Executives and ManagementLeadershipManager > 3 > SELECT DISTINCT `BUSTIER1DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `TIER3DATA` = 'Executives and ManagementLeadershipManager' > Knee > 4 > SELECT DISTINCT `BUSTIER2DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `BUSTIER1DATA` = 'Knee' > KneeDIV01 > 5 > SELECT DISTINCT `BUSTIER3DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `BUSTIER2DATA` = 'KneeDIV01' > KneeDIV01DEPT02 > 6 > SELECT DISTINCT `BUSTIER4DATA` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `BUSTIER3DATA` = 'KneeDIV01DEPT02' > KneeDIV01DEPT02GRP04 > 7 > SELECT DISTINCT `` FROM `POSITION_SETUP` WHERE `COMPANY_ID` = '3' AND `BUSTIER4DATA` = 'KneeDIV01DEPT02GRP04' > 1054 Unknown column '' in 'field list' > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > My usual approach to a problem like this to to includes a parent column in the table ID (int pk) Parent ( default null ) // no parent Item Itemtype [etc] Parent will then hold either a null if a top level item, or a structured path ( 1/10/24 ) that notes the parents of the item all the way up to the parent. That way, a single query will get you all items in that parent's lineage to whatever depth is needed by using the child's value Select * from table where parent = '1/10' Would retrieve all items that are children of a top level of 1 and a second level of 10 Hth Bastien -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
