#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    9
    Rep Power
    0

    Hierarchical query


    Hi,

    Can any body help me to solve this query. I have four following tables.

    tbl_suggestions

    s_id, us_id, pj_id, ch_id, desc
    1, 2, 1, 1, this is first suggestion
    2, 3, 1, 1, this is first suggestion
    3, 2 , 1 , 2 , this is second suggestion
    4, 2 , 2 , 1 , this is first suggestion



    tbl_user

    us_id, name
    2, John
    3, Tony

    tbl_projects
    pj_id, pj_title
    1, English
    2, History


    tbl_chapters

    ch_id, ch_title
    1, chapter 1
    2, chapter 2

    I want to show my data in this format

    JOHN(tbl_user)
    ENGLISH (tbl_projects)
    CHAPTER 1 (tbl_chapters)
    this is first suggestion (tbl_suggestions)
    CHAPTER 2 (tbl_chapters)
    this is second suggestion (tbl_suggestions)

    HISTORY
    CHAPTER 1 (tbl_chapters)
    this is first suggestion (tbl_suggestions)

    TONY(tbl_user)
    ENGLISH (tbl_projects)
    CHAPTER 1 (tbl_chapters)
    this is first suggestion (tbl_suggestions)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    92
    Rep Power
    3
    this should get you the data, though it may not be the format you are looking for:
    select name, pj_title, ch_title, desc
    from tbl_suggestions sugg
    join tbl_user uss on sugg.us_id= uss.us_id
    join tbl_projects proj on sugg.pj_id=proj.pj_id
    join tbl_chapters chapt on sugg.ch_id=chapt.ch_id
    order by name, pj_title, ch_title, s_id
    Last edited by bobert123; May 23rd, 2013 at 03:50 PM.

IMN logo majestic logo threadwatch logo seochat tools logo