Replace id with string in SQL view

Question

I have two tables...

groupid   membership_list   managerid
-------------------------------------
0         /0//1//2/         2
1         /2/               2

userid    username
------------------
0         ben
1         tom
2         dan

I'd like to display a table to for example the user 'ben' that is a list of the groups they are a member of that looks like this...

groupid   membership_list   managername
---------------------------------------
0         /0//1//2/         dan

.. so basically replacing 'manager_id' with the username for that id. I've been hacking away at this but I can't work it out - my SQL skills are clearly a bit lacking - how can I do this?

SELECT groupid, membership_list, managerid FROM blah WHERE membership_list LIKE '%/?/%'

... is about as far as I've got.

Answer 1

SELECT t1.groupid, t1.membership_list, t2.username
FROM table1 t1
INNER JOIN table2 t2 ON t1.managerid = t2.userid

That should do it. Or am I missing something here??

Answer 2

SELECT A.groupid, A.membership_list, B.managername FROM table1 A, table2 B WHERE A.managerid = B.userid and membership_list LIKE '%/?/%'

Answer 3

you need to break out membership_list column into a new table:

changed table: Groups
groupid
managerid

table users
userid
username

new table: UserGroups
groupid   
userid  

you can then do this:

SELECT
    * 
    FROM Users                 u
        INNER JOIN UserGroups ug On u.userid=ug.userid
        INNER JOIN Groups      g ON ug.groupid=g.groupid
    WHERE u.Name='Ben'

to find all of Ben's groups.

If you don't want to modify your tables, you will need a split function that will convert the multiple values in membership_list into rows. You have not mentioned the actual database you are working on and a split function is dependent on knowing that.