SQL: Trying to find individuals with a unique value only in one field-CodePudding

I apologize if this SQL question seems simple, but I have struggling with it and have tried searching the web to no avail.

I have a table (named AUDIT) that has about 1,000 unique users in my company in it. This table lists what areas of data (under the field name "Area") they have access to in our system.

Everyone has access to an "Area" named "General", which allows them to log in. Then then have will have other "Areas" that inform us what data they can access, such as Person, Payroll, Finance, Systems, etc. Within each "Area", people then have "Roles" to specify the level of access to that data that they have.

We actually have 12 "Areas" overall. For each "Area" that someone has access to, they will have a separate row in the table. For example, if you have access to "General", "Systems" and "Human Resources" then you will have 3 rows in the table. Every user has a unique ID number (appropriately named "ID").

What I'm trying to do is find people who only have access to "General". If I simply limit my query to list people who have "General", then everyone shows up.

I know how to query the table ROLES and limit it to just an "Area" of "General", but I'm unclear on how I get IDs that only have "General" and nothing else. I have also tried playing around with doing a count on the field "Area" to show me IDs who have one distinct value in that field, but haven't figured that out either.

My SQL is rusty as I've been using a nice GUI for years, but it doesn't help me here. Any assistance is appreciated!

CodePudding user response：

If everyone has "General" by default then you should be able to do something like

Select UserId
From Audit
group by UserId
having count(*)=1

Or if not something like

Select UserId
From Audit
where Area='General'
group by UserId
having min(area)=max(area)

CodePudding user response：

you can use subquery like select * from Table where Access='General' AND ID NOT In ( Select ID from Table where Access Not Equal 'General')

Or you can use group by statement on unique ID with having count == 1

CodePudding user response：

Here is a general template..

select user_name, max(role) --optional
from t
group by user_name
having max(area)=min(area) and min(area)='General';