[thelist] Database wise, how to create groups..

[Jane Williams]

Rudy <r937 at interlog.com> wrote :
>    select p.name, p.birthday, g.desc
>      from person p, group g
>    where p.something = foo
>       and g.groupid in p.grouplist
>
> when this query selects a person (p.something=foo), that row is joined to
> the groups table by matching the group ids to each of the values in the
> person's grouplist
>
> i haven't actually tested this syntax anywhere, but i'm confident it should
> work (except perhaps in databases that balk at an IN-list check involving a
> field rather than literal values)

Clever.... but I can't get it to work directly in Oracle as yet.

Maybe if I was building my query programatically in CF or something similar. But then I'd use the CF functionality to parse the list out to an array, I think.

I ended up with more like:

where instr(p.grouplist,to_char(g.groupid))>0

and to work for non-trivial cases, the string parsing would need to be more complex than that. Then there's the fun of adding an extra group to a person, or removing one... aargh! I don't want to go there!