Question 22084 in Mysql Question posted in
The official MySQL documentation can be found here.

Get records based on row value – Mysql

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.
enter image description here

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'
Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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')
    );
    Login or Signup to reply.
  3. 0

    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'
   );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search