CODEWITHSUNDEEP
mysqlsqlselect
Robert
Robert

Reputation: 61

SQL query for NOT IN operator

I need your help in writing a tricky query. The scenario is like below

Table

Location with attributes A,I,Z should be excluded(which is simple). But there is one exception. When location contains a combination of A and I attributes - it should not be excluded(i.e, Bangalore should not be excluded). Whereas Delhi (with Attributes A and Q would be excluded). So the output of the query should be like below.

Output

The query for the exclusion is simple like below 

SELECT LOCATION
FROM TABLE
WHERE ATTRIBUTE NOT IN ('A','I','Z')

But it needs to be extended to consider the exception for the attributes A and I.

Upvotes: 1

Views: 304

Answers (3)

Venkataraman R
Venkataraman R

Reputation: 13009

The below query will the locations, which are not in A,I,Z , excluding Bangalore. 

SELECT Location
FROM 
TABLE WHERE 
LOCATION NOT IN 
(SELECT LOCATION, 
FROM TABLE
WHERE ATTRIBUTE IN ('A','I','Z')
UNION ALL
SELECT LOCATION
FROM TABLE
WHERE ATTRIBUTE IN ('A','Q')
GROUP BY LOCATION
HAVING COUNT(DISTINCT ATTRIBUTE) = 2)
UNION ALL
SELECT LOCATION
FROM TABLE
WHERE ATTRIBUTE IN ('A','I')
GROUP BY LOCATION
HAVING COUNT(DISTINCT ATTRIBUTE) = 2

Upvotes: 1

Set
Set

Reputation: 171

Here's a third option:

    SELECT t.LOCATION
    FROM TABLE t
    WHERE t.ATTRIBUTE NOT IN ( 'A','I','Z' )
       OR ( t.ATTRIBUTE = 'A'
        AND EXISTS( SELECT 1
                    FROM TABLE t2
                    WHERE t.LOCATION   = t2.LOCATION
                      AND t2.ATTRIBUTE = 'I' ) )

Upvotes: 0

Mureinik
Mureinik

Reputation: 312257

One approach is to group according to the location and count the number of different attributes. By using a case statement you could match just the relevant attributes:

SELECT   location
FROM     mytable
GROUP BY location
HAVING   COUNT(DISTINCT CASE WHEN attribute IN ('A', 'I') THEN 1 END) = 2 OR
         COUNT(DISTINCT CASE WHEN attribute IN ('A', 'I', 'Z') THEN 1 END) = 0

Upvotes: 1

Related Questions