SQL statement for a tricky 2 table query

Question

I'm struggling with SQL to much to get this working, table design is given, so I can't change it.

Table 1

    username    usergroup   flag
    ----------------------------
    hans        unit1       0
    unit1       group1      NULL
    unit1       group2      NULL
    erwin       unit1       0
    jan         unit2       0
    jan         unit1       0
    unit2       group1      NULL
    unit2       group3      NULL

Table 2

    usergroup   roll_id
    --------------------
    group1      4   
    group2      5   
    group3      6   

I need a statement that fetches the roll_ids for a given user name.

Note:

Users have flag 0. Groups have flag NULL.

Example:

User jan is in unit1 and unit2, now every unit has one or more groups, stored in the same table.
So, before fetching the roll_id I need to know which groups to fetch?

Any help would be appreciated.
Thanks!

Answer 1

SELECT t2.roll_id FROM table1 t1a, table1 t1b, table2 t2
WHERE t1a.flag = 0 AND t1a.usergroup = t1b.username
AND t1b.usergroup =  t2.username AND t1a.username = 'someUsername'

2 copies of table1 to find the actual 'usergroup' for an user? and join it with table2?

Answer 2

A simple JOIN should do it. If I understand your requirements correctly, this should work:

SELECT table2.roll_id FROM table2 INNER JOIN table1 ON (table2.username = table1.usergroup) WHERE table1.username="enter a username here"

I assumed your schema was correct and that table1.usergroup matches up with table2.username. You might want to consider renaming your column names for clarity.