Михаил

Если у вас неограниченная вложенность, то только через рекурсивный запрос. MySQL 8 научился их делать. Это позволит вам рекурсивно собрать в одном из полей запроса данные из зависимых таблиц. Вот документация

Вот пример:

CREATE TABLE cat(
  id INT NOT NULL PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  parent_id INT NULL
);

INSERT INTO cat VALUE (1, 'Root1', NULL), (2, 'Root2', NULL), (3, 'Child1', 1),
(4, 'Child2', 3), (5, 'Child3', 2);

WITH RECURSIVE rcat(id, title, path) AS (
  SELECT cat.id, cat.title, CAST(cat.id AS CHAR(200)) FROM cat WHERE parent_id IS NULL
  UNION ALL
  SELECT cat.id, cat.title, CONCAT(rcat.path, ',', cat.id) FROM cat JOIN rcat ON cat.parent_id=rcat.id
)
SELECT * FROM rcat WHERE id=4;

Пример

Во-первых, стрелки на диаграмме принято рисовать в другую сторону - это таблица связи ссылается на таблицу продуктов и на таблицу фотографий.
Во-вторых, то чего вы хотите, сделать можно но это неправильный путь. Правильных путей тут два:

1. Построить запрос, который выбирает продукты по определенному условию и присоединяет к ним картинки. В этом случае у вас будет несколько записей на один продукт, отличающихся только картинками (это, я так понимаю, вы сумели сделать). Обрабатывать это не очень удобно, но зато один запрос в БД.
   
2. Выбрать сначала продукты, а затем к продуктам выбрать фотографии. Потребуется сопоставить фотографии с продуктами (по ID) на уровне приложения. Зато два запроса в БД и каждый возвращает свой набор требуемых данных.
   

Третий правильный путь - использовать ORM, которая возьмет на себя извлечение данных из БД.

Неправильный (на мой взгляд) путь - использовать агрегирующие функции, как-то так:

SELECT p.*, JSON_ARRAYAGG(f.filename) AS files FROM product p
LEFT JOIN product2file p2f ON p2f.product_id=p.id
LEFT JOIN file f ON p2f.file_id=f.id
GROUP BY p.id;

Пример

Но я считаю этот путь неправильным, потому что: с ростом сложности БД сложность подобных запросов быстро растет и тут начинают вылазить ошибки. А еще потому что вытащить из подчиненной таблицы 2 колонки можно, но порядок элементов в функции JSON_ARRAYAGG не определен, поэтому начинается возня с тем чтобы все это сопоставить. GROUP_CONCAT тоже имеет проблемы (в первую очередь ограничение длины).

Так что, мне кажется, лучше привыкать к тому что запрос может возвращать несколько строк для одной сущности и учиться с этим работать на уровне приложения, чем извращаться в запросе.

Если известно количество устройств (и соответственно количество колонок в итоговой таблице) - можно. Пример для 4 устройств:

CREATE TABLE data(
  device INT NOT NULL,
  date DATETIME NOT NULL,
  metric NUMERIC(10,2) NOT NULL
);

INSERT INTO data VALUES (1, '2022-01-01 00:00:00', 2.34),
(2, '2022-01-01 00:00:00', 1.23),
(3, '2022-01-01 00:00:00', 3.82), 
(4, '2022-01-01 00:00:00', 0.45),
(1, '2022-01-01 01:00:00', 2.00),
(2, '2022-01-01 01:00:00', 1.82),
(3, '2022-01-01 01:00:00', 3.09);

SELECT date, SUM(d1), SUM(d2), SUM(d3), SUM(d4) FROM (
SELECT date,
CASE WHEN device=1 THEN metric ELSE NULL END AS d1,
CASE WHEN device=2 THEN metric ELSE NULL END AS d2,
CASE WHEN device=3 THEN metric ELSE NULL END AS d3,
CASE WHEN device=4 THEN metric ELSE NULL END AS d4
FROM data
) nested
GROUP BY date;

https://sqlize.online/sql/mysql80/68e677a2fc577e6c...

А что вы пытаетесь тут сделать? Вот конкретно этот подзапрос (SELECT id FROM test1) он для чего? Вы выбираете все записи из таблицы test1, что, как бы, очень подозрительно. А если там будет миллион записей?

У вас в триггере есть переменные NEW и OLD, указывающие на обновленную запись в таблице test1 - вот и используйте их, чтобы определить какие записи в таблице test2 вам нужно обновить.

Подумать. Потому что такая проблема указывает что база данных денормализована, соответственно это может быть ошибка проектирования. Если это не ошибка - подумать еще раз. Если все равно так и должно быть - читать про триггеры, либо закладывать логику синхронизации таблиц в логику приложения.

Потому что тег ID=3 принадлежит не только посту 33. Вот и получаете что есть запись в которой он принадлежит и запись в которой не принадлежит. А если будет 3 поста - будет 3 такие записи (в одной принадлежит и в двух не принадлежит) и т.д.

У меня такой вопрос: зачем вам список тегов, которые не принадлежат посту? Обычно интересуют именно те, которые принадлежат. И обязательно ли это делать одним запросом?

Получить список тегов, которые принадлежат посту:

SELECT t.* FROM tags t 
JOIN posts_tags pt ON pt.tag_id=t.id
WHERE pt.post_id=:post_id

Получить список тегов, которые не принадлежат посту:

SELECT t.* FROM tags t 
LEFT JOIN posts_tags pt ON pt.tag_id=t.id AND pt.post_id=:post_id
WHERE pt.id IS NULL

Если хочется завернуть это в один запрос - добавляете колонку have_tag (в первом запросе - 1, во втором - 0) и делаете UNION.

Добавить условие WHERE title LIKE '%Спиннинг%'

Это называется XSS. Защищаются от нее путем фильтрации вводимого пользователем содержимого. Самое простое - экранировать HTML-теги, чтобы они выводились пользователю как текст, а не как HTML-код. Более сложный вариант - оставлять только безопасные теги, которые пользователь может использовать для оформления комментария (полужирный шрифт, курсив, подчеркивание, цвет шрифта и т.д.). Для этого есть готовые библиотеки, если будете писать самостоятельно - велик шанс что упустите какой-нибудь corner case и оставить "дырку" в сайте.