#1
  1. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96

    Like to have 1 row returned with lookup table


    Hi--

    Building a login system and would like to have one row returned with a person's name and access groups. My tables:

    admin_people
    -------------
    pkey
    fname
    lname
    group_name


    admin_menu
    ------------
    pkey
    item


    admin_credentials
    -----------------
    menu_key
    people_key


    What I'd like returned is a person's name, group name, and all the menu_key(s) that person has ... all in one row.

    is this a job for a subquery? Seems like a join would be faster here.

    I've gotten this far:

    PHP Code:
    select 
    concat
    (admin_people.fname," ",admin_people.lname) as fullname
    admin_people.group_name
    admin_creds.menu_key
    from admin_people
    inner join admin_creds on admin_creds
    .people_key=admin_people.pkey 
    It returns the data I want, but now how I want. Any help is seriously appreciated.
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    Take a look at the GROUP CONCAT function.
  4. #3
  5. Plays with fire
    Devshed Novice (500 - 999 posts)

    Join Date
    Aug 2003
    Location
    Barsoom
    Posts
    942
    Rep Power
    96
    perfect! Thank you!
    “Be ashamed to die until you have won some victory for humanity.” -- Horace Mann

    "...all men are created equal." -- US Declaration of Independence

IMN logo majestic logo threadwatch logo seochat tools logo