SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    May 2003
    Location
    Worcester
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL to generate dropdowns at one hit

    Can anyone suggest the SQL needed to generate the data needed for an html drop down directly from related joined tables -- with the selected flag also set. For example, I have a groups table and a users_groups table. I need to generate a table that lists ALL the groups, and indicates if a particular user is in a group(s) (ie there is a corresponding entry in the users_groups table for that user). So my resulting table would contain, groups.name, groups.group_id and SELECTED_FLAG. I'm sure it must be possible to do this directly in a single SQL statement but I keep meeting myself coming the other way when I try to figure it out...

    Thanks for any help


    Jonathan

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, try this
    Code:
    select groups.name
     , groups.group_id 
     , case when users_groups.user_id is null
     then 'n' else 'y' 
     end as selected_flag
     from groups 
    left outer
     join users_groups 
     on groups.group_id = users_groups.group_id
     and users_groups.user_id = 'value'
    edit: i'm a-gettin' pretty tired of how this wysiwyg editor inserts tabs into my code -- by definition, code means leave it the hell alone, i want it to line up the way i want it to line up:
    Code:
    select groups.name
    ____ , groups.group_id 
    ____ , case when users_groups.user_id is null
    ___________ then 'n' else 'y' 
    ______ end as selected_flag
    _ from groups 
    left outer
    _ join users_groups 
    ___ on groups.group_id = users_groups.group_id
    __ and _________________  users_groups.user_id = 'value'
    rudy
    http://r937.com/


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
  •