SELECT id, (SELECT count(*) FROM log_like_authoriz, (SELECT * FROM log_like_authoriz WHERE (user_id, id) in (SELECT user_id, MAX(id) FROM log_like_authoriz WHERE run_id = r.id GROUP BY user_id) HAVING type_like <> 0) s WHERE t.id = s.id) AS like_count FROM run r ORDER BY id DESC

Error> SQL (1054): Unknown column 'r.id' in 'where clause'

how to correctly determine this ID value from the RUN table

 SELECT count(*) FROM log_like_authoriz, (SELECT * FROM log_like_authoriz WHERE (user_id, id) in (SELECT user_id, MAX(id) FROM log_like_authoriz WHERE run_id = 19 GROUP BY user_id) HAVING type_like <> 0) s WHERE t.id = s.id

This request receives all likes of type 1, for a specific run (for example, 19) by the last user_id entry (i.e. with the maximum id)

enter image description here

  • Unfortunately, the table from the outer part of the query can be accessed only at the first nesting level of the subquery. The only way out is to rewrite the subquery in such a way that there would be no more nestings in it. I can’t understand the number of what you think and if it can be more than 1 (there is a certain selection by id, if id in this table is unique, then it seems to me that there can be no more than 1 output, and then the query can be much Simplify) - Mike
  • As an option, since you still get all the records from run, transfer the entire subquery from the list to the from part and get the values ​​for all the run records at once by grouping the records in the subquery by run_id - Mike
  • I added, I just spent all night on these requests to choose likes, I don’t even know how to redo it, it works. If the table from the external part of the query can only be accessed at the first level, then a different approach is needed - Den Kapone

1 answer 1

Unfortunately, the table inside the query is available only at the first nesting level of subqueries. On this request must be altered in such a way that it would not be necessary to refer to the external table at deep levels.

Your request can be altered as:

 SELECT run_id as id, count(1) FROM ( SELECT run_id, user_id FROM log_like_authoriz GROUP BY run_id, user_id HAVING MAX(id) = MAX( (type_like<>0) * id ) ) x GROUP BY run_id

If you need additional fields from the run table, you must perform its join to the subquery "x".

Since any logical operation (for example, <> ) in MySQL results in 0 or 1. That condition (type_like<>0) * id gives 0 if type_like = 0 or gives id for other type_like. Applying to this expression MAX we get the maximum id whose type_like is not null. If the received id is equal to the id of the last record for the given user (within the run_id limits), then this is a suitable record for us to count.

  • It seems to be working, added WHERE run_id = run.id and removed run_id as id, it turned out one digit as it should for a specific run. But do not tell me what the query returns if there is no such run_id, it would be good to return 0. I tried to add: CASE WHEN count (1) = NULL THEN 0 WHEN count (1)> 0 THEN count (1) END AS like_count in the first SELECT does not return 0 - Den Kapone 3:53 pm
  • IFNULL (count (1), 0) doesn't work either - Den Kapone
  • @DenKapone To return a NULL, you need to "rely" on something, you can not return a record that does not exist. You need to write select r.id, count(x.user_id) FROM run r LEFT JOIN (подзапрос) x ON x.run_id=r.id - Mike
  • Yes, I understood, thank you very much for the help and the time spent on me)) - Den Kapone
  • @DenKapone Yes, there is nothing :) And about the time ... Next time, please give an example of the data in the form of text. Even a regular sign with a space separator can be pushed into the table very quickly. But to interrupt the lines from the picture for the test example (I took 19 run_id) is somewhat tiring :) - Mike