How to use SELECT CASE with subquery

Question

I have a problem with my SELECT query. I try to retrieve a tagValue name "CallsBy", but only if the value is "Entry point" - otherwise I want to compute the calls by another sub query.

I have 3 tables :

t_object: with all the classes
t_operation: with all the operations, link with t_object.Object_ID = t_operation.Object_ID
t_operationtag: with all the tag value for each operation, link with t_operation.OperationID = t_operationtag.ElementID

Here is a demo with my tables I have:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b6a1672948db816aefb23b8dc8d8e01d

The result for the class 1 would be :

Name Operation   | CallsBy
-----------------+------------------------------------
class1Operation1 | class2Operation2, class3Operation1
class1Operation2 | class2Operation3
class1Operation3 | class2Operation3

The result for the class 2 would be :

Name Operation   | CallsBy
-----------------+----------------------------------------------------
class2Operation1 | class1Operation1, class3Operation1,class3Operation3
class2Operation2 | Entry point of Tabidi
class2Operation3 | class1Operation1

The result for the class 3 would be :

Name Operation   | CallsBy
-----------------+---------------------------------------
class3Operation1 | class1Operation2, class3Operation1
class3Operation2 | Entry point of Tabada
class3Operation3 | class2Operation1

Here is the sub query to compute the CallsBy if is not a "Entry point" :

select 
    t2.name, group_concat(t1.name) 
from
    t_operation t1
left join 
    (select to3.ElementID, to2.name
     from t_object to1
     left join t_operation to2 on to1.Object_ID = to2.Object_ID
     left join t_operationtag to3 on find_in_set(to2.Name, to3.VALUE)
     where to3.Property = 'Calls'
       and to1.Object_ID = '1') t2 on t1.OperationID = t2.ElementID
where 
    t2.ElementID is not null
group by 
    t2.name;

I try to write the query but I didn't get any result...

Sample data, desired results, and an explanation of the logic would all help. — Gordon Linoff, Feb 24 '20 at 17:20
Try to use Outer Applys instead of a nested Select: https://stackoverflow.com/questions/36869221/cross-outer-apply-in-mysql — Robin, Feb 24 '20 at 17:36

Gharbad The Weak · Answer 1 · 2020-02-25T05:41:54.403

First off let me say that I believe your database table structure and relationships might not be optimal, it seems complex for just 3 tables.

Also, I believe there is a difference between the data you listed in this post and the data that you listed in the db fiddle example. I created all of the tables and populated them with the data you gave and the results of the query I came up with don't exactly match the data you listed above as the expected result.

However, I believe this query will get you what you want: if the corresponding t_operationtag.Property = 'CallsBy' and the t_operationtag.VALUE contains Entry point then it returns the t_operationtag.VALUE else it group concats the t_operation.Name values for the given object id:

SELECT top.Name,
    CASE WHEN topt2.VALUE IS NOT NULL THEN topt2.Value
    ELSE GROUP_CONCAT(topt1.VALUE)
    END AS CallsBy
FROM t_operation AS top
JOIN t_operationtag AS topt1 ON topt1.ElementID = top.OperationID   
LEFT JOIN t_operationtag AS topt2 ON topt2.ElementID = top.OperationID
    AND topt2.Property = 'CallsBy'
    AND topt2.VALUE LIKE '%Entry point%'
JOIN t_object AS obj ON obj.Object_ID = top.Object_ID
WHERE obj.Object_ID = 1 -- or 2 or 3
GROUP BY top.Name, topt1.PropertyID, topt2.PropertyID
ORDER BY top.Name

I hope this helps.

How to use SELECT CASE with subquery

1 Answers1