Get records based on row value-CodePudding

I want to get the records based on the row value. Please refer to the attached image for a table overview with records.

If there is no Execution Project in the column projecttype for a specific ESAProjectID then take the row with values projecttype='Group Project' .

otherwise

if both Execution Project and Group Project are found for a specific ESAProjectID then take only projecttype='Execution Project'

In the attached image I have marked in green color records are the expected result.

I tried this SQL but no luck

SELECT DISTINCT a.ESAProjectID, a.projecttype
  FROM test1 a
 INNER JOIN test1 b
    ON a.ESAProjectID = b.ESAProjectID
 WHERE a.projecttype = 'Group Project'

CodePudding user response：

If the version of the DB is 8.0 , then you can count by using a window aggregation function per each ESAProjectID value such as

WITH tt AS
(
 SELECT t.*, 
        SUM(projecttype='Execution Project') OVER (PARTITION BY ESAProjectID) AS sm
   FROM test1 AS t
)
SELECT ESAProjectID, ProjectID, projecttype
  FROM tt
 WHERE sm > 0 AND projecttype = 'Execution Project'
    OR sm = 0 AND projecttype = 'Group Project'

Demo

CodePudding user response：

Here's another way:

select a.* from test1 a
    join
    (select ESAProjectID,
sum(case when projecttype = 'Execution Project' then 1 else 0 end) count
from test1 group by ESAProjectID) b
on (a.ESAProjectID = b.ESAProjectID)
where a.projecttype = (
    IF(b.count = 0, 'Group Project', 'Execution Project')
    );

CodePudding user response：

Since 'Execution Project' and 'Group Project' are the only possible values in the column projecttype, you can do it with NOT EXISTS:

SELECT t1.ESAProjectID, 
       t1.projecttype 
FROM test1 t1
WHERE t1.projecttype = 'Execution Project' 
   OR NOT EXISTS (
     SELECT *
     FROM test1 t2
     WHERE t2.ESAProjectID = t1.ESAProjectID 
       AND t2.projecttype = 'Execution Project'
   );