CODEWITHSUNDEEP
sqlsql-server
Princess
Princess

Reputation: 443

SQL Group By? Needing a sub select?

I'm looking to find all groups that have users that are both registered and unregistered. I was trying to use a Group By but i'm missing something. Thanks

Select U.IsRegistered, UG.UserGroupId
From tblGroup G
Join tblUserGroup UG on UG.GroupId = G.GroupId
Join tblUser U on U.UserId = UG.GroupId
Group By U.IsRegistered, UG.UserGroupId

tblUser
-------+----------+-------------
UserId | UserName | IsRegistered
1      | Bob      | 1
2      | Sally    | 0
3      | Jeff     | 1


tblGroup
--------+----------
GroupId | GroupName
1       | Blue
2       | Green


tblUserGroup
------------+---------+-------
UserGroupId | GroupId | UserId
1           | 1       | 1
2           | 1       | 2
3           | 2       | 3

Upvotes: 0

Views: 66

Answers (3)

John Allen
John Allen

Reputation: 46

If I am understanding this correctly, we have two conditions that need to be met.First, we need to know that the group has an unregistered user. Second, we need to know that the group has a registered user. So, if we create a statement that has the first condition and inner join that with a statement that has the second condition, that would leave us with only the desired groups. 

Select UnRegistered_Group.UserGroupId
From (
    SELECT UG.UserGroupID
    FROM tblGroup G
    INNER Join tblUserGroup UG on UG.GroupId = G.GroupId
    INNER Join tblUser U on U.UserId = UG.GroupId
    WHERE U.IsRegistered = 0
) AS UnRegistered_Group
Inner Join (
    SELECT UG.UserGroupID
    FROM tblGroup G
    INNER Join tblUserGroup UG on UG.GroupId = G.GroupId
    INNER Join tblUser U on U.UserId = UG.GroupId
    WHERE U.IsRegistered = 1
) AS Registered_Group
On UnRegistered_Group.UserGroupID = Registered_Group.UserGroupID;

Upvotes: 0

Pops
Pops

Reputation: 498

SELECT GroupName
From tblGroup G
Join tblUserGroup UG on UG.GroupId = G.GroupId
Join tblUser U on U.UserId = UG.UserID
GROUP BY GroupName
HAVING AVG(cast(IsRegistered as float)) < 1
AND AVG(cast(IsRegistered as float)) > 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do this using group by and having:

Select U.IsRegistered, UG.UserGroupId
From tblUserGroup UG Join
     tblUser U
     on U.UserId = UG.GroupId
Group By UG.UserGroupId
Having sum(IsRegistered) > 0 and sum(1 - IsRegistered) > 0;

The having clause simply specifies that there is at least one registered user and one non-registered user.

Note that the tblGroup is not needed in the query.

Upvotes: 3

Related Questions