How to filter out a table using SQL query?-CodePudding

I am new to SQL and having a hard time to filter out a table using SQL query. I have a table as below


CATEGORY     |      NAME         |     UID         |  LOCATION
------------------------------------------------------------------------
Planning     |      Test007      | AVnNDZEGp5JaMD  |  USER
Planning     |      Test007      | AVjNDZEGp5JaMD  |  SITE
Planning     |      Test007      | NULL            |  NULL
Develop      |      Test008      | AZkNDZEGp5JaMD  |  USER
Develop      |      Test008      | NULL            |  NULL
Workspace    |      Test10       | QWrNjwaEp5JaMD  |  USER
Workspace    |      Test10       | NULL            |  NULL
Workspace    |      Test10       | NULL            |  SITE

I want to filter out this table on one condition. For each unique "NAME" in table I want to exclude the row with "LOCATION" = NULL if there exists a row with "LOCATION" = SITE.

For example the one with "NAME" as "Test007" have 3 entries with "LOCATION" as "USER, SITE, NULL". So I want to check if there exist a entry with "SITE", If yes then exclude the "NULL" from output result.

This is the result I expect -


CATEGORY     |      NAME         |     UID         |  LOCATION
------------------------------------------------------------------------
Planning     |      Test007      | AVnNDZEGp5JaMD  |  USER
Planning     |      Test007      | AVjNDZEGp5JaMD  |  SITE
Develop      |      Test008      | AZkNDZEGp5JaMD  |  USER
Develop      |      Test008      | NULL            |  NULL
Workspace    |      Test10       | QWrNjwaEp5JaMD  |  USER
Workspace    |      Test10       | NULL            |  SITE

Entries of "Test007" & "Test10" with location as NULL are excluded.

CodePudding user response：

Here is one approach using window functions:

WITH cte AS (
    SELECT *, SUM(LOCATION = 'SITE') OVER (PARTITION BY NAME) cnt
    FROM yourTable
)

SELECT CATEGORY, NAME, UID, LOCATION
FROM cte
WHERE cnt = 0 OR LOCATION IS NOT NULL;

CodePudding user response：

Use NOT EXISTS:

SELECT t1.*
FROM tablename t1
WHERE t1.LOCATION IS NOT NULL
   OR NOT EXISTS (
     SELECT *
     FROM tablename t2
     WHERE t2.NAME = t1.NAME AND t2.LOCATION = 'SITE'
   );

See the demo.