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
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 CIf Project owner only himself working inside the project then keep it, like Project A and Project E
If Project owner doesn’t have his name inside col2 then exclude the project like Project D
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 )