3

What i am looking for is I have a table like this Table 1. 

COLUMN1 | COLUMN2 | Source
    --------------- ---------------
     1    |   X   |      a    
     1    |   X  |       b     
     1    |   X   |      c   
     2    |   Y   |      a     
     2    |   Y   |      b     
     3    |   Y   |      c     
     3    |   Y   |      b
     4    |   Y   |      c
     5    |   Z   |      b

Result should be

  COLUMN1 | COLUMN2  
    --------------- 
     3    |   Y 
     4    |   Y  
     5    |   Z

What i am trying to achieve is I am trying to group the result set based on Column1 and Column2 and if any of the set contains value "a" for the column name "Souce" i need not consider that data set , say 

1 X
2 Y

Please advise on this.

shanavascet
  • 589
  • 1
  • 4
  • 18

2 Answers2

4

You can use conditional aggregation for this:

SELECT column1,
  column2
FROM your_table t
GROUP BY column1,
  column2
HAVING COUNT(CASE WHEN source = 'a' THEN 1 END) = 0;
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
-1

You can achieve the desired result using below query:

SELECT DISTINCT COLUMN1, COLUMN2 FROM TABLE1 WHERE Source <> 'a'

Sushil Soni
  • 7
  • 1