CODEWITHSUNDEEP
mysqlsql
Dade
Dade

Reputation: 241

SQL filtering on single column with multiple conditions

Pretty new to SQL and I'm not completely familiar with the proper syntax.

I have a table that has a column which has a one to many relationship. I need to filter the column for one value, but not the others.

Here's what the data looks like:

| USER KEY | USER ID |
| ------   | ------- |
| 11672    |   SSO   |
| 11672    |   SSO   |
| 11672    |  DIRECT |
| 11203    |   SSO   |
| 11205    |   SSO   |
| 11206    |  DIRECT |

So you can see a user key can have both SSO and DIRECT or just SSO or just Direct . I need to filter the user key so it:

A. Includes those USER KEYS with only DIRECT

B. Exclude all those USER KEYS that have both SSO and DIRECT

I started with this select statement:

SELECT *
FROM USER_ID
WHERE USER_ID_TYPE = 'DIRECT'
OR (USER_ID_TYPE != 'SSO');

This gives me back ALL the users with Direct regardless of whether they have both SSO and DIRECT. I don't know what the syntax is to filter out the other USER_KEY that has both. Most of the tutorials I've read are pretty basic and don't go this deep into complex filtering like this.

Any help would be appreciated.

P

Upvotes: 2

Views: 8078

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You can achieve your desired result using some aggregation also

select USER_KEY
from USER_ID
group by USER_KEY
having sum(USER_ID_TYPE = 'DIRECT') > 0
and sum(USER_ID_TYPE = 'SSO') = 0

DEMO

Upvotes: 1

cosmos
cosmos

Reputation: 2303

SELECT *
FROM USER_ID U
WHERE USER_ID_TYPE = 'DIRECT'
AND NOT EXISTS 
    (SELECT 1 FROM USER_ID UsubQ WHERE U.USER_ID_KEY = UsubQ.USER_ID_KEY 
                               AND USER_ID_TYPE = 'SSO')

Upvotes: 0

Barmar
Barmar

Reputation: 782488

Your query is performing both tests against a single row, not for all the rows with the same USER_KEY. You need something like this:

SELECT *
FROM USER_ID AS u1
WHERE USER_ID_TYPE 'DIRECT'
AND NOT EXISTS (
    SELECT *
    FROM USER_ID AS u2
    WHERE u1.USER_KEY = u2.USER_KEY
    AND u2.USER_ID_TYPE = 'SSO')

or the equivalent LEFT JOIN

SELECT u1.*
FROM USER_ID AS u1
LEFT JOIN USER_ID AS u2 ON u1.USER_KEY = u2.USER_KEY AND u2.USER_ID_TYPE = 'SSO'
WHERE u1.USER_ID_TYPE = 'DIRECT'
AND u2.USER_KEY IS NULL

Upvotes: 5

Related Questions