Question 113350 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to filter out a table using SQL query?

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search