SQL to generate dropdowns at one hit

[crown]

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

[r937]

yes, try this

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:

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/