Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Contents Sort 3 Levels of Data

    I Have a table of Data (WikiData)

    WikiID int
    ParentID int
    sTitle varchar(50)
    sDescription varchar(MAX)

    There will be three levels of data imposed at the Application Layer

    Level 1: ParentID = 0
    An Item Like Geography
    Level 2: ParentID = a Level 1 WikiID
    A sub Topic like Volcanoes
    Level 3: ParentID = Level 2 WikiID
    A bottom Topic like Pyroclastic Flows

    I Need a SQL statement that Will Produce the Output where The output will be produced like this:
    Level 1
    Level 2
    Level 2
    Level 2
    Level 1
    Level 2
    Level 2

    I Built this but its wrong and has no order by Group by Statements
    Select * from WikiData where ParentID = 0 or ParentID IN (Select * from WikiData where ParentID = 0)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i can give you a result set that looks like this

    Level1A Level2X Level31
    Level1A Level2X Level32
    Level1A Level2X Level33
    Level1A Level2Y Level36
    Level1A Level2Y Level37
    Level1A Level2Z Level3x
    Level1A Level2Z Level3y
    Level1A Level2Z Level3z
    Level1B Level2P Level3a
    Level1B Level2P Level3b
    Level1B Level2P Level3c
    Level1B Level2Q Level3p
    Level1B Level2Q Level3q
    Level1B Level2R Level35
    Level1B Level2R Level36
    Level1B Level2R Level37

    as you loop over the result set, you detect control breaks at each level, and can then print them out nicely indented

    Level1A
    - Level2X
    - - Level31
    - - Level32
    - - Level33
    - Level2Y
    - - Level36
    - - Level37
    - Level2Z
    - - Level3x
    - - Level3y
    - - Level3z
    Level1B
    - Level2P
    - - Level3a
    - - Level3b
    - - Level3c
    - Level2Q
    - - Level3p
    - - Level3q
    - Level2R
    - - Level35
    - - Level36
    - - Level37
    Last edited by r937; 12-22-07 at 01:18.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Don't want the third level they will be grouped on the page, just need the first two levels for the contents? If possible?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select root.WikiID  as root_WikiID
         , root.sTitle  as root_sTitle
         , root.sDescription  as root_sDescription
         , down1.WikiID  as down1_WikiID
         , down1.sTitle  as down1_sTitle
         , down1.sDescription  as down1_sDescription
      from WikiData as root
    left outer
      join WikiData as down1
        on down1.ParentID = root.WikiID
     where root.ParentID = 0
    order 
        by root.sTitle
         , down1.sTitle
    see Categories and Subcategories

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

  5. #5
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Cool Thanks

    Can't check the output, away with family, will try it out when im back home

  6. #6
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    IT works well but a Title with Parent ID = 0 becomes invalid when it has child items
    the Geography item associated with 13 becomes difficult to code because it is both the Parent and the Child, I can code this if need be but would rather the query returned a simpler output?

    1 Art & Culture NULL
    2 Business in Rotorua NULL
    4 Geography / Geology / Ecology 13 - Contains two records?
    4 Geography / Geology / Ecology 12
    3 History


    Ideally I would like the Title such as art & Culture to show as a line of NULL values then the children underneath?

  7. #7
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Thanks Got it

    I think Ive got it I disected your statement, and made it work thank you

    SELECT
    dwn.*
    FROM wikidata as root INNER JOIN wikidata as dwn
    ON dwn.ParentID = root.WikiID
    where root.ParentID = 0
    Union All
    select * from WikiData where ParentID = 0

  8. #8
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Except it Doesn't order the data correctly

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Nate1
    ... a Title with Parent ID = 0 becomes invalid when it has child items
    ??


    Quote Originally Posted by Nate1
    the Geography item associated with 13 becomes difficult to code because it is both the Parent and the Child, I can code this if need be but would rather the query returned a simpler output?
    did you try the query i wrote? i showed exactly how you should transform the result set into your indented hierarchy -- i even used colour!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Yep tried it even stuck it into my Datalist looked really good, except as soon as a Parent item (Parent = 0) the item becomes troublesome to program as a parent item
    eg

    Art & Culture Topic 1
    Art & Culture Topic 2
    Business in Rotorua Topic 1
    Business in Rotorua Topic 2

    The Parent items become difficult because they are directly associated in the output with each of the child items which is OK but not ideal,

    Ideally the set would be displayed from the select like so

    Art & Culture
    Art & Culture Topic 1
    Art & Culture Topic 2
    Business in Rotorua
    Business in Rotorua Topic 1
    Business in Rotorua Topic 2
    ....
    But only to Level 2 of the Relationship as described Earlier? Im being picky because the ideal layout allows me to produce the menu I'd like exactly how i think it should be using a datalist, the other options I have tried datalist in datalist master child relations, and the other SQL code Ive posted displays sets of data that are either not ordered correctly or display the data in an undesirable way.

    If you could help sort the output of this data the problem would be solved. AS shown above?
    PHP Code:
    SELECT 
         dwn
    .*
    FROM wikidata as root INNER JOIN wikidata as dwn
    ON dwn
    .ParentID root.WikiID
    where root
    .ParentID 0
    Union All
    select 
    from WikiData where ParentID 

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i guess i'm still not making myself clear

    i am recommending that you not try to munge the sql into generating the output of several levels into a single column

    i am recommending that you run this instead:

    PHP Code:
    SELECT root.*
         , 
    dwn.*
      
    FROM wikidata as root 
    LEFT OUTER
      JOIN wikidata 
    as dwn
        ON dwn
    .ParentID root.WikiID
     WHERE root
    .ParentID 
    except instead of the dreaded, evil "select star" you would code aliases so that you can distinguish between the two sets of identically named columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Yeah I see what you mean, its not ideal, and a little cumbersome, I will get it too work thanks for your help.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh i forgot, add this --
    Code:
    ORDER BY root.name, dwn.name
    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
  •