All geek questions in one place

MYSQL alias in the subquery where the condition

I have a problem with aliases in subqueries.

I have a request:

SELECT `id` as `t`, `title`, `once`, (select count(`id`) from `orders_files` where `order_id`='116815' and `type`=`t` and (`version`='41924') ) as `count`, (select count(`id`) from `orders_files` where `order_id`='116815' and `type`=`t` ) as `count_in_order` FROM `orders_filetypes` WHERE `required`=1 ORDER BY `sort`, `title`

, which starts and runs on an older version of mysql (5.6.22) and does not work on a new version of mysql (5.7.9) . mysql error is "# 1054 - Unknown column 't' in 'where clause'.

The configuration files are identical, and what the problem is, I do not understand.

We updated the mysql server and many queries stopped working. Please tell me, is this function deprecated in the new mysql or can it be installed /etc/my.cnf?

it is impossible to find and fix hundreds of thousands of lines of code where such requests can be used, therefore it is preferable to enable this feature on a new server.

Thank you!

+5
source share
1 answer

why not use a table alias and call a column by its name as follows:

 SELECT `id` as `t`, `title`, `once`, (select count(`id`) from `orders_files` where `order_id`='116815' and `type`=outer_table.`id` and (`version`='41924') ) as `count`, (select count(`id`) from `orders_files` where `order_id`='116815' and `type`=outer_table.`id` ) as `count_in_order` FROM `orders_filetypes` as outer_table WHERE `required`=1 ORDER BY `sort`, `title`
0
source

Source: https://habr.com/ru/post/1237500/

More articles:


All Articles