Skip to content
Advertisement

Filter out certain rows based on condition

I have this Input Table:

+-------------+--------------+-------+
| ProjectName | ProjectOwner | Col2  |
+-------------+--------------+-------+
| A           | Sara         | Sara  |
| B           | Jack         | Maria |
| B           | Jack         | Jack  |
| C           | Laura        | May   |
| C           | Laura        | Laura |
| D           | Paul         | Jack  |
| E           | May          | May   |
| E           | May          | May   |
+-------------+--------------+-------+

I want to check each project separately then exclude

  1. If the project owner has his name inside col2 and other people with him, then remove the row which is repetitive Jack Jack,

    a. For example, In Project B Jack has his name inside Col2 and he is working with Maria , so remove Jack Jack

    b. Also remove Laura Laura in Project C

  2. If Project owner only himself working inside the project then keep it, like Project A and Project E

  3. If Project owner doesn’t have his name inside col2 then exclude the project like Project D

  4. Remove duplicate like Project E

Output Table:

+-------------+--------------+-------+
| ProjectName | ProjectOwner | Col2  |
+-------------+--------------+-------+
| A           | Sara         | Sara  |
| B           | Jack         | Maria |
| C           | Laura        | May   |
| E           | May          | May   | 
+-------------+--------------+-------+

I tried this here but the results didn’t show me Project A and E.

Advertisement

Answer

Please try this code:

select distinct  *
from mytable m
where (ProjectOwner!=Col2 or not exists(select 1 from mytable m1 where m1.ProjectName=m.ProjectName and  m1.ProjectOwner=m.ProjectOwner and m.Col2!=m1.Col2) )
and ProjectName in (select ProjectName from mytable where ProjectOwner=Col2 )

Demo

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement