Best way to limit results in MySQL with user subcategories
- by JM4
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.
