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 would like to display a table, for example, for the user "ben", which is a list of groups of which they are members that look like this:

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

.. therefore basically replacing "manager_id" with the username for this id. I was hacked at this, but I can’t figure 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 '%/?/%'

... as far as I know.

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?

Answer 2

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

Answer 3

member_list :

changed table: Groups
groupid
managerid

table users
userid
username

new table: UserGroups
groupid   
userid  

:

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 Ben's groups.

If you do not want to modify your tables, you will need a split function that converts multiple values ​​in a member list to strings. You did not specify the actual database you are working on, and the split function depends on its knowledge.