Mysql case if result found in first case do not run next-CodePudding

I have two tables where I need to return matching results on basis of CASE statement. If the query returns data in the first case. We need to return data. We do not need to check the following case statements.

This is what I tried

Select
    CASE 
        WHEN foo.Marks ='Two' AND t1.Subject = 3 THEN foo.UniqueValue
    END as 'Result',
    CASE 
        WHEN foo.Marks ='0' AND t1.Subject = 3 THEN foo.UniqueValue
    END as 'Result1',
    CASE 
        WHEN foo.Marks IS NULL AND t1.Subject IS NULL THEN foo.UniqueValue
    END as 'Result2'
    from (
select t1.Marks, t1.Subject, t1.Student, foo.UniqueValue from `table1` t1
inner join `table2` t2 on t1.Student = t2.GroupName
where t2.GroupID = 2
)foo;

id	Marks	Subject	Student	UniqueValue
1	Two	3	FOO	AIR1
2	0	3	FOO	AIR2
3	NULL	NULL	FOO	AIR3

id	GroupID	GroupName
1	2	FOO
2	3	BAR
3	7	FOO123

Level 1 -> If Marks and Subject matched it should return that AIR1.

Level 2 -> If Marks is 0 and Subject is 3 should return AIR2.

If both above cases do not find any result then the below case should be checked.

Level 3 -> If Marks is NULL and Subject is NULL should return AIR3

If in Level1, the matching row is found it should return that Level2,3 should not be checked.

In the final, I should only get a single row matching the above levels by priority.

CodePudding user response：

If GroupID is known,

SELECT t1.UniqueValue
FROM table1 t1
JOIN table2 t2 ON t1.Student = t2.GroupName
WHERE t2.GroupID = 2
ORDER BY 
  CASE WHEN t1.Marks = 'Two' AND t1.Subject = 3 THEN 1
    WHEN t1.Marks = '0' AND t1.Subject = 3 THEN 2
    WHEN t1.Marks IS NULL AND t1.Subject IS NULL THEN 3
    ELSE 4 END ASC
LIMIT 1

To get one result for each GroupID

WITH cte AS (
 SELECT t1.UniqueValue, ROW_NUMBER() OVER(PARTITION BY t2.GroupID ORDER BY CASE WHEN t1.Marks = 'Two' AND t1.Subject = 3 THEN 1
    WHEN t1.Marks = '0' AND t1.Subject = 3 THEN 2
    WHEN t1.Marks IS NULL AND t1.Subject IS NULL THEN 3
    ELSE 4 END ASC) rn
 FROM table1 t1
 JOIN table2 t2 ON t1.Student = t2.GroupName
)
SELECT UniqueValue
FROM cte
WHERE rn = 1

CodePudding user response：

Why don't you just do ELSE instead of END and new CASE?

Select
    (CASE 
        WHEN foo.Marks ='Two' AND t1.Subject = 3 THEN foo.UniqueValue
    ELSE
        WHEN foo.Marks ='0' AND t1.Subject = 3 THEN foo.UniqueValue
   ELSE
        WHEN foo.Marks IS NULL AND t1.Subject IS NULL THEN foo.UniqueValue
    END) Result
    from (
select t1.Marks, t1.Subject, t1.Student, foo.UniqueValue from `table1` t1
inner join `table2` t2 on t1.Student = t2.GroupName
where t2.GroupID = 2
)foo;