Results 1 to 5 of 5

Thread: My Sql

  1. #1
    Registered User
    Join Date
    Apr 2008
    Posts
    1
    Thanks
    0
    Thanked
    0 times in 0 posts

    Question My Sql

    Hi all,
    My question is..i have created one table with parent-child relationship...like
    Cat1,Cat2,Cat3 and so on.....
    where cat2 is child of cat1..and cat 3 is child of cat2...it is complete heirarchy,now i need to merge all of these categories in one column...and create a reference link in order to access parent...that means...something like this...
    Serial_no Category_Name Parent_Number
    1 Cat1.1 0
    2 Cat1.2 0
    3 Cat1.3 0
    4 Cat2.1 1
    5 Cat2.2 2
    6 Cat3.1 4
    7 Cat3.2 5
    Is there anybody know which query i am supposed to run in order to get this end result?? I would appreciate if anybody could help me
    Last edited by Megha; 01-04-2008 at 07:45 PM. Reason: Mistake

  2. #2
    0iD
    0iD is offline
    M*I*A 0iD's Avatar
    Join Date
    Jul 2003
    Location
    Happy Llama Land
    Posts
    13,247
    Thanks
    1,435
    Thanked
    1,209 times in 757 posts
    • 0iD's system
      • Motherboard:
      • Leave my mother out of it!
      • CPU:
      • If I knew what it meant?
      • Memory:
      • Wah?
      • Storage:
      • Cupboards and drawers
      • Graphics card(s):
      • Slate & chalk
      • PSU:
      • meh
      • Case:
      • Suit or Brief?
      • Operating System:
      • Brain
      • Monitor(s):
      • I was 1 at skool
      • Internet:
      • 28k Dialup

    Re: My Sql

    Welcome to heXus chap

    Fro a more relevant answer to your question post it here Software and web development - HEXUS.community discussion forums Where those who 'know' are more likely to hang out
    [
    Quote Originally Posted by Blitzen
    When I say go, both walk in the opposite direction for 10 paces, draw handbags, then bitch-slap each other!

  3. #3
    Admin (Ret'd)
    Join Date
    Jul 2003
    Posts
    18,481
    Thanks
    1,016
    Thanked
    3,208 times in 2,281 posts

    Re: My Sql

    Moved to a more relevant forum.

    And welcome to Hexus, Megha.

  4. #4
    Senior Member manwithnoname's Avatar
    Join Date
    Dec 2005
    Posts
    1,050
    Thanks
    17
    Thanked
    26 times in 25 posts

    Re: My Sql

    Is this an academic exercise or is it a a real life problem - if its a real life problem can you expand on the problem and hopefully there will be a limit to the hierarchy.

    If there is a limitless hierarchy then I'm not sure you can write a query in mySQL (in oracle you can use the CONNECT BY syntax - but this is not supported by mySQL) I think you would need to write procedure to extract the data.

    If there is a limit to the hierarchy you can join the table to itself multiple times, the number of level of hierarchy to be exact. Assume the table is call my_big_category_table and the examples are oracle syntax not mySQL

    1 level of hierarchy (ie none)
    Code:
    select lev0.Serial_no,
           lev0.Category_Name,
           lev0.Parent_Number
    from   my_big_category_table lev0 /* alias for the table is lev0 */
    where  lev0.Parent_Number is null
    2 levels of hierarchy
    Code:
    select lev0.Serial_no,
           lev0.Category_Name,
           lev0.Parent_Number,
           lev1.Category_Name,
           lev1.Parent_Number
    from   my_big_category_table lev0, /* alias for the table is lev0 */
           my_big_category_table lev1  /* alias for the table is lev1 */
    where  lev0.Parent_Number is null
    and    lev0.Serial_no = lev1.Parent_Number (+) /* this is the outer join syntax in oracle */
    3 levels of hierarchy
    Code:
    select lev0.Serial_no,
           lev0.Category_Name,
           lev0.Parent_Number,
           lev1.Category_Name,
           lev1.Parent_Number,
           lev2.Category_Name,
           lev2.Parent_Number
    from   my_big_category_table lev0, /* alias for the table is lev0 */
           my_big_category_table lev1, /* alias for the table is lev1 */
           my_big_category_table lev2  /* alias for the table is lev2 */
    where  lev0.Parent_Number is null
    and    lev0.Serial_no = lev1.Parent_Number (+) /* this is the outer join syntax in oracle */
    and    lev1.Serial_no = lev2.Parent_Number (+) /* this is the outer join syntax in oracle */
    and so on ... not so great if there are lots of levels of hierarchy

  5. #5
    Gentoo Ricer
    Join Date
    Jan 2005
    Location
    Galway
    Posts
    11,048
    Thanks
    1,016
    Thanked
    944 times in 704 posts
    • aidanjt's system
      • Motherboard:
      • Asus Strix Z370-G
      • CPU:
      • Intel i7-8700K
      • Memory:
      • 2x8GB Corsiar LPX 3000C15
      • Storage:
      • 500GB Samsung 960 EVO
      • Graphics card(s):
      • EVGA GTX 970 SC ACX 2.0
      • PSU:
      • EVGA G3 750W
      • Case:
      • Fractal Design Define C Mini
      • Operating System:
      • Windows 10 Pro
      • Monitor(s):
      • Asus MG279Q
      • Internet:
      • 240mbps Virgin Cable

    Re: My Sql

    Which lots of data recursion involved, you're way better off building an stree or something similar anyway. I haven't tried to test any limits on MySQL's joining abilities, but it's practically unlimited, it's just not tidy for deeply nested hierarchies.
    Quote Originally Posted by Agent View Post
    ...every time Creative bring out a new card range their advertising makes it sound like they have discovered a way to insert a thousand Chuck Norris super dwarfs in your ears...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 17
    Last Post: 15-07-2007, 03:59 PM
  2. SQL Process Manager
    By madman045 in forum Software
    Replies: 7
    Last Post: 14-09-2006, 10:28 AM
  3. Member Database SQL / PHP
    By realm in forum Software
    Replies: 4
    Last Post: 03-01-2006, 06:48 PM
  4. SQL Server 2000
    By Fatboy in forum Software
    Replies: 37
    Last Post: 17-08-2005, 07:05 PM
  5. SQL Server Connection string?
    By Stoo in forum Software
    Replies: 7
    Last Post: 04-05-2005, 05:48 PM

Posting Permissions

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