#1
  1. Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    421
    Rep Power
    12

    Circular join help?


    Hi.

    I've been given some raw data to produce a report with. But there seems to be a dynamic number of columns which could potentially be produced depending upon how many 'sub' collections a collection has.....and I'm trying to do this with MS Access. I'm struggling with the joining process, so any help would be appreciated.

    Table Design:

    COLLECTIONS
    CollectionID
    CollectionName

    STRUCTURE
    StructureID (PK)
    CollectionID (References Collections.CollectionID)
    SubCollectionID (References Collections.CollectionID)

    One collection may have many subcollections.

    Thanks.
    Captain Planet.
  2. #2
  3. Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    421
    Rep Power
    12
    I really don't think I've explained this properly. Leave it with me and I'll think some more....
    Captain Planet.
  4. #3
  5. Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    421
    Rep Power
    12
    Originally Posted by Captain Planet
    I really don't think I've explained this properly. Leave it with me and I'll think some more....
    Please see an example attached of what I'm after. There are two example tables with data, and one expected query result. Thanks for any help. I'm lost with this one...
    Attached Files
    Captain Planet.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Code:
    SELECT L1.CollectionName
         , L2.CollectionName
         , L3.CollectionName
         , L4.CollectionName
         , L5.CollectionName
      FROM (((((((
           Collection AS L1
    LEFT OUTER
      JOIN Structure AS S2
        ON S2.CollectionId = L1.CollectionId
           )
    LEFT OUTER
      JOIN Collection AS L2
        ON L2.CollectionId = S2.SubCollectionId
           )
    LEFT OUTER
      JOIN Structure AS S3
        ON S3.CollectionId = L2.CollectionId
           )
    LEFT OUTER
      JOIN Collection AS L3
        ON L3.CollectionId = S3.SubCollectionId
           )
    LEFT OUTER
      JOIN Structure AS S4
        ON S4.CollectionId = L3.CollectionId
           )
    LEFT OUTER
      JOIN Collection AS L4
        ON L4.CollectionId = S4.SubCollectionId
           )
    LEFT OUTER
      JOIN Structure AS S5
        ON S5.CollectionId = L4.CollectionId
           )
    LEFT OUTER
      JOIN Collection AS L5
        ON L5.CollectionId = S5.SubCollectionId
     WHERE L1.CollectionId NOT IN
           ( SELECT SubCollectionId FROM Structure )
    results --
    Code:
    Collection A   Collection B   NULL           NULL           NULL
    Collection A   Collection C   Collection F   Collection G   NULL
    Collection A   Collection C   Collection F   Collection H   Collection J
    Collection A   Collection C   Collection F   Collection I   NULL
    Collection A   Collection C   Collection H   Collection J   NULL
    Collection D   NULL           NULL           NULL           NULL
    Collection E   NULL           NULL           NULL           NULL
    notice that D and E are included as "topmost" items in their collection tree (you had missed these in your sample output)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo