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!
2 copies of table1 to find the actual 'usergroup' for an user? and join it with table2?