help with query 1

[RobBroekhuis]

Hi all,
Please bear with me, I'm very new with SQL, so this is pretty rudimentary. I have a database with two tables:

users (with fields name and groups)
registry (with field name and others)

A user can belong to one or more groups, A, B, C, etc. The groups field is simply a concatenation of the groups he is in, e.g. "BF".

I have one user selected. Now what I'm looking for is a query that returns all records from table registry where name refers to a user in one of the same groups as the selected user. For example:

users:
Carl BF
Rob BD
Amy ED
Max CE

registry:
Carl 1
Carl 5
Rob 3
Amy 7
Amy 9
Max 11
Max 13

If the selected user is Rob, then the query should return all of the registry records belonging to Carl, Rob, and Amy, but not those belonging to Max, because Max and Rob do not share a group.
Am I making sense?


Rob

 

[vanekl]

select r.name, r.others
from users u1, users u2, registry r
where u2.name = r.name
and u1.name = 'Rob'
and u2.groups REGEXP concat("\[",u1.groups,"\]&quot

The backslashes are necessary only so that the brackets
show up in this post. They can be deleted in your real
query.

 

[RobBroekhuis]

Thanks! That works. Now I need to find a decent mySQL reference and figure out why


Rob

 

[hos2]

http://www.mysql.com

perhaps

 

[RobBroekhuis]

Their explanation of REGEXP is skimpy, to say the least. I'll still need a real primer
(that's not a request for one, by the way - I'll restrict my pleas for help on this here forum to more specific issues)


Rob