Best way to limit results in MySQL with user subcategories

Posted by JM4 on Stack Overflow See other posts from Stack Overflow or by JM4
Published on 2012-10-01T17:45:06Z Indexed on 2012/10/09 21:39 UTC
Read the original article Hit count: 165

Filed under:

I am trying to essentially solve for the following:

1) Find all users in the system who ONLY have programID 1.

2) Find all users in the system who have programID 1 AND any other active program.

My tables structures (in very simple terms are as follows):

users

userID  | Name
================
1       | John Smith
2       | Lewis Black
3       | Mickey Mantle
4       | Babe Ruth
5       | Tommy Bahama

plans

ID | userID | plan | status
---------------------------
1  |  1     | 1    | 1
2  |  1     | 2    | 1
3  |  1     | 3    | 1
4  |  2     | 1    | 1
5  |  2     | 3    | 1
6  |  3     | 1    | 0
7  |  3     | 2    | 1
8  |  3     | 3    | 1
9  |  3     | 4    | 1
10 |  4     | 2    | 1
11 |  4     | 4    | 1
12 |  5     | 1    | 1

I know I can easily find all members with a specific plan with something like the following:

SELECT * FROM users a JOIN plans b ON (a.userID = b.userID) WHERE b.plan = 1 AND b.status = 1

but this will only tell me which users have an 'active' plan 1.

How can I tell who ONLY has plan 1 (in this case only userID 5) and how to tell who has plan 1 AND any other active plan?

Update: This is not to get a count, I will actually need the original member information, including all the plans they have so a COUNT(*) response may not be what I'm trying to achieve.

© Stack Overflow or respective owner

Related posts about mysql