sql. how to select rows based on values of other columns?-CodePudding

There is a problem with my sql. My table is the following:

Id                column_1         column_2          
1                 name_1              yes            
2                 name_1              no               
3                 name_1              yes              
4                 name_2              no              
5                 name_2              no              
6                 name_3              yes              
7                 name_3              yes              
8                 name_3              yes              
9                 name_3              yes                
10                name_4              yes              
11                name_4              no

The output that I want to get:

 Id               column_1         column_2          
1                 name_1              yes            
2                 name_1              no               
3                 name_1              yes                           
10                name_4              yes         
11                name_4              no

I want to get rows where the column values are 'yes' and 'no'. If there is only 'yes' and if there is only 'no' - then they do not need to be elected.

Note: I was thinking to use decode() or case when.. however as I understood it is not a solution.

CodePudding user response：

We can try to use the condition aggregate function with the window function in a subquery, then compare that with your logic.

SELECT
  id,   
  column_1, 
  column_2  
FROM (
    SELECT t1.*,
           COUNT(CASE WHEN column_2 = 'yes' THEN 1 END) OVER(PARTITION BY column_1) yesCnt,
           COUNT(CASE WHEN column_2 = 'no' THEN 1 END) OVER(PARTITION BY column_1) noCnt,
           COUNT(*) OVER(PARTITION BY column_1) allCnt
    FROM T t1
) t1
WHERE  (yesCnt <> allCnt AND noCnt <> allCnt)

sqlfiddle

CodePudding user response：

you could use exists for this

here is the fiddle https://www.db-fiddle.com/f/bhBbqgnnexoKhESvAJB5c1/0

   select * from mytable a
    where exists (select 'Roll' from mytable b where a.column_1 = b.column_1 and b.column_2 = 'yes')
    and exists (select 'Tide' from mytable c where a.column_1 = c.column_1 and c.column_2 = 'no')