Get Users(only Allowed) In Group In Single Mysql Query

From following tables i have to fetch the list of users allowed in the specified group upon the basis of following logic. Case 1 : all users qualifies when there is no match (no r

Solution 1:

This should work, or something close to it.

select u.user_id, g.group_id, g.code
from MyGroup g
    cross join users u 
    left join user_group allowed ON u.user_id = allowed.user_id AND g.group_id = allowed.group_id AND allowed.exclusion = 0
    left join user_group allowbydefault ON u.user_id = allowbydefault.user_id
where (allowed.user_id isnot null OR allowbydefault.group_id is null)
-- and g.group_id = 1 -- uncomment toget everyone authorized forgroup #1
-- and u.user_id = 35 -- uncomment toget every group that user #35is authorized forgroupby u.user_id, g.group_id, g.code

I used SQL Server to test this but this should at least give you another point of attack for MySQL.