Fetching rows that have a particular value, but not others, is a fairly common task in database development and administration. It sounds like a walk in the park, but limiting the results to those rows that possess one value to the exclusion of others is trickier than it sounds. The reason is, while it's trivial to filter out values using the != not equals or NOT IN comparison operators, these only hide values rather than tell us whether or not an entity possesses these other values. The good news is that there's an easy way to do it. Read on to find out how!
Selecting Users by Role
One thing that all databases - and most applications - have are users. In particular, database users tend to have different roles. (Although application users may also have roles.) Here's an example of such a table in Navicat's Table Designer:
In this case, the role_id would be a Foreign Key (FK) that links to a roles table that would store additional information about each role. In the users table, the inclusion of the role_id leads to the possibility of having multiple rows for each user. Keep that in mind, because that idea will be revisited a little later on...
The Wrong Way to List Users That Have One Role but No Others
If were were to now list users who possess a particular user role, and only that role, we might be tempted to write something like the following:
The problem is that the above query only lists users who have a role_id of 1. It does nothing to address whether or not they have other role_ids as well. Moreover, adding another criteria such as AND role_id NOT IN (2,3,4,5,6,7,8,9) does nothing to help because 1 is obviously not any other number!
So, how do we limit users to those that have only a role_id of 1 and no others?
The RIGHT Way to List Users That Have One Role but No Others
Well, this is technically not "The RIGHT Way" because there are surely others. This solution consists of counting the number of rows for each user. The idea is that, if a user has the role_id that we're interested in AND only has one row in the table, then they're someone we want to see in the results.
We can obtain a count of user_ids for each user in the table using a GROUP BY. Then, the HAVING clause can check that a user only has one row and that his/her one role_id is the one we want:
Now we only see the one user who has a role_id of 1, and no others!
Conclusion
One good thing about this approach is that it can easily be modified to find rows that contain multiple values or more than a certain number of rows in the table.
Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!
