How to inner join result of stored function?

Question

I am searching for all day with no success so I decided to ask.

I will very simplify structure as much as possible to ask for essence.

I have function:

mysql> SELECT set_of_ids_to_names('1:2:3:4:5', ':') AS `res`;
+-------------------------------+
| res                           |
+-------------------------------+
| NameA:NameB:NameC:NameD:NameE |
+-------------------------------+

I have table:

mysql> SELECT * FROM `tbl_tool`;
+----+-----------------+---------+
| ID | Tool            | ID_name |
+----+-----------------+---------+
|  1 | Tool_1          | 1:2:3:4 |
|  2 | Tool_2          | 2:4:5   |
|  3 | Tool_3          | 4:5     |
|  4 | Tool_4          | 3       |
+----+-----------------+---------+

The result I would like to achieve is to have view called 'v_tool' so once I selet it I get:

mysql> SELECT * FROM `v_tool`;
+----+-----------------+-------------------------+
| ID | Tool            | Name                    |
+----+-----------------+-------------------------+
|  1 | Tool_1          | NameA:NameB:NameC:NameD |
|  2 | Tool_2          | NameB:NameD:NameE       |
|  3 | Tool_3          | NameD:NameE             |
|  4 | Tool_4          | NameC                   |
+----+-----------------+-------------------------+

This what I tried is:

SELECT `tbl_tool`.`ID`, `tbl_tool`.`Tool`, `Name` FROM `tbl_tool` 
INNER JOIN (SELECT set_of_ids_to_names((SELECT `ID` FROM `tbl_tool` WHERE `ID` = `tbl_tool`.`ID`), ':') AS `Name`) AS `aaa`

I know that it is wrong, but I just could not find idea how to pass proper value to function 'set_of_ids_to_names'.

Big thank you in advance.

The way you are calling `set_of_ids_to_names`, it seems like it is a scalar function. So couldn't you just `SELECT Tool, set_of_ids_to_names(ID_name, ':') AS Name FROM tbl_tool`? — Ruud Helderman, Mar 04 '19 at 11:51
OMG, thanks a lot !!! This was driving me crazy all day, but it couldn't be simpler :) — Daniel Duchna, Mar 04 '19 at 12:47
OK great, I was afraid I had missed the point, that's why I didn't post it as an answer right away. I will do so now anyway, as it seems like a useful answer to others in the future. — Ruud Helderman, Mar 04 '19 at 13:38

score 0 · Answer 1 · answered Mar 04 '19 at 13:37

Looking at the original function call you made:

SELECT set_of_ids_to_names('1:2:3:4:5', ':') AS `res`

It is important to note the function call appears in the SELECT clause, not in the FROM clause. This suggests set_of_ids_to_names is a scalar function, not a table-valued function.

When querying table tbl_tool, you can do the exact same thing: call set_of_ids_to_names in the SELECT clause.

SELECT Tool, set_of_ids_to_names(ID_name, ':') AS Name
FROM tbl_tool

For table-valued functions, the situation is different of course. SQL Server has CROSS APPLY for that, in MySQL you'd probably have to join the table with a subquery encapsulating the function call.

How to inner join result of stored function?

1 Answers1