SQL query 1

[jondubya]

This is probably very simple, like most of my questions but I can't narrow my search on the web.

I've two tables
Users and UserGroups; Users store information about users, Usergroups store groups subscribed to by each user; primary/referential key between them is userID; this is a one to many relationship with each user allowed to have more than one usergroup.

I'm trying to find a query in MySQL 3.x that will give me all the users who subscribe to two groups.

As a start I can do:

Select * From Users, UserGroups WHERE Users.userID = UserGroups.userID WHERE UserGroups.groups = "GROUPA" OR UserGroups.groups = "GROUPB";

This will give me anyone who is subscribed to either group, from this I could just work out those who have a name that appears twice, but surely there's a better way?

 

[ethorn10]

select count(*) as total, 
       a.userid
from users a inner join
          usergroups b on a.userid = b.userid
group by a.userid

should give you a list of each userid and the number of groups they subscribe to.

select count(*) as total, 
       a.userid
from users a inner join
       usergroups b on a.userid = b.userid
group by a.userid
having count(*) = 2

should give you a list of userids that are subscribed to exactly 2 groups.

hope this helps.

 

[r937]

this will give you the users that are subscribed to both selected groups:

select Users.name
  from Users
inner
  join UserGroups
    on UserGroups.userID = Users.userID
 where UserGroups.groups
       in ('GROUPA','GROUPB')
group
    by Users.name
having count(*) = 2

r937.com | rudy.ca

 

[jondubya]

Thank you both for your responses, they are ways in which I would never have thought to construct a select statement and work well. I'm using Rudy's way as it displays only the users who have groupA and groupB in their profile. Do you guys think in terms of set theory when you create statements or just write them with another kind of mental picture?

 

[r937]

my own mental picture is very much like a spreadsheet -- each table has rows and columns, very "two-dimensional"

the key is understanding the requirements, and from your description this meant users with two specific rows, so right away this tells me it requires GROUP BY user and seeing how many of the necessary rows, as filtered by the WHERE clause, are present, so this check is done in the HAVING clause

how would you change the query to show users who belong to at least 3 of a given 5 groups? that's right, the WHERE clause lists all 5, and the HAVING clause looks for count(*)>=3

easy, once you recognize the pattern


r937.com | rudy.ca

 

[ethorn10]

I only wrote mine the way I did to avoid hard-coding the two groups, but that's clearly what you were truly after. Sorry about that...

Anyway, I only knew how to write that query as a result of rudy's help at another point in time. He's a personal knowledge-base...deserving of a star!