SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multi-layered results

    Hello,

    I posted this on the PHP forum but I was told I would have better success here.

    I need to write a query that produces multi-layered results.

    For example I have moderators who moderate groups of users, the group names, the user's names who belong in the groups and then some data on each of those users I need it to return in a format that groups the data under the user, the user under the group and the group under the moderator in the following format:

    Moderator: Randy
    Group: Group 1
    Name 1
    data
    data data
    Name 2
    data
    data data

    Group 2
    Name 1
    data
    data data
    data data data

    Moderator: Joe
    Group: Group 1
    Name 1
    data
    data data
    Name 2
    data
    data data
    I have three database tables, one of which (users) needs to be queried twice.

    First query: select id, fname, lname from users where permissions = 'mod'
    Table: users
    columns: id, fname, lname

    Second query: select id, group_name from groups where mod='"result from first query"'
    Table: groups
    Columns: id, group_name

    Third query: select id, fname, lname from users where group='result from second query'
    table: users (second pass)
    columns: id, fname, lname

    Final query: select text_data from data where user_id ='Result from third query'.
    Table: data
    columns: text_data

    Thank you in advance for any help rendered.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select m.id as mod_id
         , m.fname as mod_fname
         , m.lname as mod_lname
         , g.id as g_id
         , g.group_name as g_name
         , u.id as u_id
         , u.fname as u_fname
         , u.lname as u_lname
         , t.text_data
      from users m
      join groups g
        on g.mod = m.id
      join users u
        on u.group = g.id
      join data t
        on t.user_id = u.id
     where m.permissions = 'mod'
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2004
    Location
    Boston
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    Code:
    select m.id as mod_id
         , m.fname as mod_fname
         , m.lname as mod_lname
         , g.id as g_id
         , g.group_name as g_name
         , u.id as u_id
         , u.fname as u_fname
         , u.lname as u_lname
         , t.text_data
      from users m
      join groups g
        on g.mod = m.id
      join users u
        on u.group = g.id
      join data t
        on t.user_id = u.id
     where m.permissions = 'mod'
    I will try that, thank you.


Bookmarks

Posting Permissions

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