Results 1 to 12 of 12

Thread: Cascaded query

  1. #1
    Join Date
    Jul 2004
    Location
    UK
    Posts
    24

    Question Unanswered: Cascaded query

    Hi,

    I have a database for recording employees timesheets.
    I have (for example) an employee heirachy of :
    Code:
                              Mr A
               |----------------|----------------|
             Mr B             Mr C             Mr D
                           |----'----|      |----'----|
                         Mr E      Mr F   Mr G      Mr H
                       |---'---|
                     Mr I    Mr J
    (yes, OK, it's a very sexist organisation, and they're all in witness protection!!!)

    In my employees table (Users) I record the ID of each persons direct Line Manager. ie:

    Code:
    ID   Name    LMID
    -----------------
    1    Mr A     1
    2    Mr B     1
    3    Mr C     1
    4    Mr D     1
    5    Mr E     3
    6    Mr F     3
    7    Mr G     4
    8    Mr H     4
    9    Mr I     5
    10   Mr J     5
    What I need to be able to do, is create a query where by supplying the ID of any person in the table, I can produce a list with that person in and all the people under that person (no matter how many levels that may be). I will use this list to populate a combo of all the people whose timesheets that person has the right to see.

    For example,
    If I pass ID=5 (Mr E), I want to return 5,9 & 10 ( Mr E, Mr I & Mr J).
    If I pass ID=3 (Mr C), I want to return 3,5,6,9,10 (Mr C, Mr E, Mr F, Mr I & Mr J).
    If I pass ID=1 (Mr A), I want to return all the names.

    Can anyone suggest the SQL I need to produce this? I know it will require a nested SELECT of some sort, but I am having trouble getting my head around it!

    Any help would be much appreciated!

    Many thanks (in advance)

    Qpid.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no matter how many levels? not with that data model, sorry

    if you can accept a query that only goes down a maximum fixed number of levels, e.g. seventeen, then you can write a query (with seventeen LEFT OUTER JOINs)

    otherwise, do a search for joe celko's nested set model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    The only way I can think of would be in code by returning the IDs of the people with the required LMID, and then do the same for these IDs AS LMIDs until no records are returned.

    All these IDs will need remembered (in a dynamic array!) at each stage, and then a query constucted in code with all these ID values as the filter criteria.

    A bit long winded (I cetainly don't have time at present) but I feel it is possible if it is really required.

    Anyone else any thoughts ?

    MTB

    MTB

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    doing queries in a loop is horribly inefficient, and yet many people take this route because they are unwilling to commit to some maximum number of levels for the query

    Qpid, how many levels do you have? your diagramn shows 4 levels, but how many do you reasonabl;y expect at most? eight? fourteen? if it is an employee hierarchy, there's got to be some reasonable maximum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    if it is an employee hierarchy, there's got to be some reasonable maximum
    Unless qpid works in the British public sector of course
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2004
    Location
    UK
    Posts
    24
    I do actually work iin the British Public Sector !!!

    It's just for one department (at the moment) where there are four levels. It is possible though, that a new level of middle-management might be added (lol) or someone could come in under someone at the bottom level.

    Qpid :-)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, now we are making some good progress

    okay, you have 4 levels

    just to be safe, write the query for 7 levels

    one query, no looping, no inefficiency, and above all, you get to keep your current data structure and don't have to learn about nested sets and lft and rgt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Qpid
    It is possible though, that a new level of middle-management might be added (lol) or someone could come in under someone at the bottom level.
    Either you do work in British public sector or you are well informed on how it operates. I like your optimism that there will be only one extra level of middle management though.

    Personally I would think of the maximum number of managerial levels you think might be necessary for the organisation to operate efficiently & effectively then apply the BPS coefficent (times it by 3.27) for future proofing

    Rudy of course knows I am being facetious - 7 sounds super duper do.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    This is a similar format to a "Bill Of Material". To make it truly dynamic (so that the number of levels doesn't matter) I used some home grown code that queries all the "children" of the start point and writes the results to a temporary table, then queries each child, writing the results to the temp table and loops until there are no more children.

    In each case, I also note the level and the parent so the report can assemble the list grouped correctly.

    The temp table now holds the entire structure, correctly goruped and sorted.

    Although it is "looped queries" it is pretty quick since the "Master Table" is only 2 fields of interest: ParentKey, ChildKey. Besides, I wonder how many hidden queries Jet creates running the 7 outer join query ... I currently have a system running this where the "Master Table" has over 6700 records with some bill's going over 6 levels deep and it processes in a matter of seconds.

    An advantage of this is that in the case of manufacturing, each material can be assigned to multiple parents, and can even be assigned to different parents in the same tree. It can also be done in reverse, to see where a material is used (or in your case, who the chain of command is for any given person).

    This may be a bit more complicated than what you are looking for, but it may be of interest to somebody else looking for something similar.

    tc

  10. #10
    Join Date
    Jul 2004
    Location
    UK
    Posts
    24
    HI guys!

    Thanks for all your advice.

    I bottled it in the end and went for MikeTheBike's inefficient method as I realised in the end that I don't actually need a query, as I can dynamically populate a combo box.

    The code I ended up using is:

    Code:
    Set rs = CurrentDb.OpenRecordset("Users")
    rs.MoveFirst
    strCanSee = ""
    Do Until rs.EOF
      LMid = rs("LMid")
      Do
        If LMid = LogonID Or rs("ID") = LogonID Then
          strCanSee = strCanSee & rs("ID") & ";'" & rs("Name") & "';"
          Exit Do
        End If
        oldLMid = LMid
        LMid = DLookup("[LineManager]", "Users", "[ID]='" & oldLMid & "'")
      Loop Until LMid = oldLMid
      rs.MoveNext
    Loop
    rs.Close
    comboCanSee.RowSource = strCanSee
    The LogonID is that of the person who's viewing rights I am trying to ascertain. I've found that this works no matter how many levels there are.
    It cycles through everyone in the table, then works up that person's Line-Manager structure to see if the Logged-on person has any rights (it finally drops out of the inner-loop when it gets to to top of the tree as I've assigned that person's Line Manager as themself).

    I'm currently working with about 40+ users and it works pretty quick. Could get a bit slow if it become hundreds of users, but I'll cross that bridge when the middle-management increases!!!!

    Once again, thanks for all your advice (although I'm still open to offers of a more efficient way ).

    Qpid.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Qpid
    Once again, thanks for all your advice (although I'm still open to offers of a more efficient way ).
    I imagine Rudy is wiping coffee off his screen as he gets to this bit. Personally I think a SQL solution is slicker but what the hey? if code does it.

    The only refinement I would suggest is that DLookup is not so great when used in a loop - the more loops the more you will notice the lag. Personally I would populate a second recordset and use a filter on that instead.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey, this is microsoft access, it's not like efficiency matters...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •