How to retrieve the records which will Match all rows with the first subquery

Question

Hi Need help to resolve some complicated query. I'll send the input username (say test ) to the query. I have to list the other users with the same role of the username test and also those usernames should be match with all the groupid of the username test. If the other user has more groups than username test is not an issue, but should match all the groupdid exists in the username test. I tried the following query. It wil return no rows if all the groupids matches. But, I need the username those who have match with all the group id instead of returning no rows for the exact match.

select 
  group_id 
from 
  user_info us ,group_privilege_details gp 
where 
  login_name='test' 
  and us.user_id = gp.user_id 
EXCEPT
select 
  group_id 
from 
  user_info u ,group_privilege_details g 
where login_name !='test' 
and role_id in (select role_id 
                from user_info 
                where login_name ='test')
and group_id in (select group_id 
                 from user_info us ,group_privilege_details gp 
                 where login_name='test' 
                 and us.user_id = gp.user_id ) 
and g.user_id = u.user_id

Thanks in advance. And sorry for long explanation

Could you also give an example listing 5 rows (or so) of input data and the output rows that should generate? — Johan, Oct 20 '11 at 11:40
I'm trying to decide which is easier to comprehend, the code or the explanation ... — ypercubeᵀᴹ, Oct 20 '11 at 11:40
I think this is a relational division problem. See this question with many (I mean a lot) different approaches to similar problem: http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation — ypercubeᵀᴹ, Oct 20 '11 at 11:43
And this article, **Divided We Stand: The SQL of Relational Division**: http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ — ypercubeᵀᴹ, Oct 20 '11 at 11:44

score 3 · Accepted Answer · answered Oct 20 '11 at 14:28

If I understand correctly you want:

select all users,
        not being user test,
        with same role as user test,
        where not exists a group where user test is a member of
            and the other user is not a member of

In SQL that is something like the following. I used the following abbreviations: OU other user, TU test user, TUG test user's groups, OUG other user's groups.

select OU.login_name, OU.user_id
    from user_info OU, user_info TU
    where OU.user_id <> TU.user_id 
        and OU.role_id = TU.role_id 
        and TU.login_name = 'test'
        and not exists (
            select * from group_privilege_details TUG
                where TUG.user_id = TU.user_id
                    and TUG.group_id not in (
                        select group_id 
                            from group_privilege_details OUG 
                            where OUG.user_id = OU.id
                    )
        )

+1 For the 'pseudo-SQL' - I might start employing that method! — El Ronnoco, Jul 04 '12 at 08:18

How to retrieve the records which will Match all rows with the first subquery

1 Answers1