SELECT with a variable

I have 3 tables :

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

I try to get an list for each of my operation (1) with all the operation (2) which calls the operation (1) :

So I write this request :

SELECT op1.Name
FROM t_object AS allClass 
LEFT JOIN t_operation AS op1 ON allClass.Object_ID = op1.Object_ID 
INNER JOIN t_operationtag AS function_tag ON ((op1.OperationID = function_tag.ElementID) 
AND (function_tag.VALUE LIKE CONCAT("%", "MSA_Se_SECU_ComputeHMAC", "%") 
AND function_tag.Property = 'Calls'))
GROUP BY op1.OperationID

Here sample data to test the query : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3711bb22e52c4528898ece82833042ce

But my problem is the query return only the “CallsBy” for the operation “class2Operation1”

I would like to get all the CallsBy for all the operation of the select Class. The result for the class 1 would be :

• Name Operation | CallsBy
• class1Operation1 | class2Operation2, class3Operation1
• class1Operation2 | class2Operation3
• class1Operation3 | class2Operation3

Class 2

• Name Operation | CallsBy
• class2Operation1 | class1Operation1, class3Operation1,class3Operation3
• class2Operation2 | class1Operation1
• class2Operation3 | class1Operation1

Class 3

• Name Operation | CallsBy
• class3Operation1 | class1Operation2, class3Operation1
• class3Operation2 | class1Operation3
• class3Operation3 | class2Operation1

My only variable to select a class I could use is WHERE myClass.Object_ID = ‘1’.

This request works well but I would like to change automaticaly “MSA_Se_SECU_ComputeHMAC” by using a variable. I try this :

SELECT op1.Name
FROM t_object AS allClass2 LEFT JOIN t_operation AS op2 ON allClass2.Object_ID = op2.Object_ID,
t_object AS allClass 
LEFT JOIN t_operation AS op1 ON allClass.Object_ID = op1.Object_ID 
INNER JOIN t_operationtag AS function_tag ON ((op1.OperationID = function_tag.ElementID) AND (function_tag.VALUE LIKE CONCAT("%",op2.Name, "%") AND function_tag.Property = 'Calls'))
WHERE allClass2.Object_ID = '783'
GROUP BY op1.OperationID

But this don’t work because He didn’t know “op2.Name”, and I have no idea if it’s possible to do ? maybe with a variable ? Hope its enough clear. Here some sample data to test the query : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3711bb22e52c4528898ece82833042ce

Answer

After some extensive discusion with OP this is the solution:

select t2.name "Name Operation"
       , group_concat(t1.name) CallsBy
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;

Here is the DEMO of the solution

And here are some notes to his question and code in question:

First query or how you call it request is not ok. It returns error that is telling you that you can not group by OperationID if it is not in select statement (or in agregate function in select statement).

SELECT op1.Name
FROM t_object AS allClass 
LEFT JOIN t_operation AS op1 ON allClass.Object_ID = op1.Object_ID 
INNER JOIN t_operationtag AS function_tag ON ((op1.OperationID = function_tag.ElementID) 
      AND (function_tag.VALUE LIKE CONCAT("%", "MSA_Se_SECU_ComputeHMAC", "%") 
      AND function_tag.Property = 'Calls'))
GROUP BY op1.OperationID

If you replace, in that first query, this line:

GROUP BY op1.OperationID

with this line:

GROUP BY op1.Name

The code will work but the results maybe will not be the same…

Second query has more than one problem. First one is the same as in the first query(group by).

SELECT op1.Name
FROM t_object AS allClass2 
LEFT JOIN t_operation AS op2 ON allClass2.Object_ID = op2.Object_ID
--, t_object AS allClass 
LEFT JOIN t_operation AS op1 ON allClass.Object_ID = op1.Object_ID 
INNER JOIN t_operationtag AS function_tag ON ((op1.OperationID = function_tag.ElementID) 
      AND (function_tag.VALUE LIKE CONCAT("%",op2.Name, "%") 
      AND function_tag.Property = 'Calls'))
WHERE allClass2.Object_ID = '783'
GROUP BY op1.OperationID

Further more, if you see up in the code, the line that is commented is not logical there. It is not a join, join condition and I do not know what you wanted to do there…

Also, in one part of the code your alias is allClass and it should be allClass2.

If you change all that you will get this:

SELECT op1.Name
FROM t_object AS allClass2 
LEFT JOIN t_operation AS op2 ON allClass2.Object_ID = op2.Object_ID
LEFT JOIN t_operation AS op1 ON allClass2.Object_ID = op1.Object_ID 
INNER JOIN t_operationtag AS function_tag ON ((op1.OperationID = function_tag.ElementID) 
      AND (function_tag.VALUE LIKE CONCAT("%",op2.Name, "%") 
      AND function_tag.Property = 'Calls'))
WHERE allClass2.Object_ID = '783'
GROUP BY op1.Name

This works. But the results that it gives is another story…