Как исправить запрос?

Question

[Rednuof]

Я новичок в sql и вообще редко имею дело со сложными запросами.
Есть у меня старая база, которую сделал дургой программист в пьяном угаре. Теперь все завязано на ней и структуру не поменяешь ибо все сломается. Приходиться работать с ней.
У меня есть две таблицы: в одной хранятся записи о тестах, во второй учебные материалы для подготовки к этим тестам.
Структура примерно такая:
TESTS:
| ID | YEAR | DESCRIPTION |
| -- | ---- | ----------- |
| 0 | 2023 | some text |
| .. | ... | ... |
| N | 2024 | some text |

test_prep_materials:

| ID | PATH | TID |
| -- | -------------------- | --- |
| 0 | /home/user/file0.pdf | 0 |
| .. | ... | ... |
| M | /home/user/fileM.pdf | 0 |

Учебные материалы могут быть одинаковые (пути) для разных тестов. То есть один физический файл /home/user/data.pdf может быть учебным материалов для N тестов и, соответсвено, иметь N записей в таблице test_prep_materials.
Так вот мне нужно из этих таблиц сделать выборку путей до учебных материалов, которые используются ТОЛЬКО в тестах определенного года. Например только материалы, которые используются в тестах 2022 года и не используются никакими другими.
В результате я хочу получить два набора данных
1) айди записи в таблице test_prep_materials и собственно пути.
2) список уникальных путей из 1 набора.
Вся эта информация мне нужна чтобы:
1) Удалить записи о материалах из таблицы test_prep_materials
2) Физически удалить файлы с диска.

На данный момент у меня получилось написать такой запрос:

Select     tpm.PATH
From       tests t 
Left Join  test_prep_materials tpm ON(tpm.TID = t.ID)
Group By   tpm.PATH
HAVING     Max(t.YEAR) < 2024 and Min(t.YEAR) > 2022

Этот код успешно возвращает мне список путей, но без tpm.id. А если я пытаюсь добаивть tpm.id - очевидно возникает ошибка в GROUP BY. (incompatible with sql_mode=only_full_group_by)

Кто-то может меня спасти от этого ужаса и помочь дописать запрос?

Answer 1

[Алексей Уколов]

Поскольку id после группировки может быть несколько, то нужно использовать аггрегатные функции для их получения. В данном случае - GROUP_CONCAT().

Ну или, если очень хочется получить каждый id в отдельной строке, можно обернуть ваш запрос в другой:

select * from test_prep_materials where PATH in (
  select tpm.PATH
  from tests as t 
  left join test_prep_materials as tpm on tpm.TID = t.ID
  group by tpm.PATH
  having max(t.YEAR) < 2024 and min(t.YEAR) > 2022
)

P.S. Логичнее, вроде, джойнить тесты к материалам, а не наоборот.

Comments

[Rednuof]

Не совсем понимаю, как использовать Group_concat().
Тем не менее мой запрос возвращет из базы 231 результат, тот который вы написали выше - 571, что-то кажется не так

[Алексей Уколов]

Не совсем понимаю, как использовать Group_concat()

Я за вас в вашу голову понимание не вложу. Что именно вам непонятно? Что вы попробовали, почему не получилось?

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

Разумеется, они возвращают разное количество результатов - мой запрос разгруппировывает отфильтрованные значения обратно.

[Rednuof]

Алексей Уколов,

Разумеется, они возвращают разное количество результатов - мой запрос разгруппировывает отфильтрованные значения обратно

то есть я правильно понимаю, что у меня было грубо говоря униклаьное значение PATH, а после вашей обертки в дургой запрос - получилось, что нашлись все дубликаты?

Я за вас в вашу голову понимание не вложу. Что именно вам непонятно? Что вы попробовали, почему не получилось?

Ну, я так понимаю должно было получиться нечто такое?

Select     tpm.Description, GROUP_CONCAT(tpm.id)
From       tests t 
Left Join  learningfiles tpm ON(tpm.TID = t.ID)
Group By   tpm.Description
HAVING     Max(t.YEAR) < 2024 and Min(t.YEAR) > 2022;

[Алексей Уколов]

то есть я правильно понимаю, что у меня было грубо говоря униклаьное значение PATH, а после вашей обертки в дургой запрос - получилось, что нашлись все дубликаты?

У вас было M строк для каждого PATH, к которому было привязано N ID (просто вы их не видели). В моём запросе M*N строк, в каждой из которых связка конкретного ID и PATH.

Ну, я так понимаю должно было получиться нечто такое?

Как минимум. У функции есть ещё разные параметры, но вы можете их не использовать, если они вам не нужны.

[Rednuof]

Алексей Уколов, Понял, спасибо вам большое за помощь, вроде бы разобрался. Скажите пожалуйста, а что можно еще поизучать потыкать, чтобы более менее писать запросы такой сложности? а то совсем беда какая-то... Если вам не трудно, был бы очень рад, если бы вы поделились парой источников.

[Алексей Уколов]

Я это всё изучал давно и уже не помню каких-то конкретных ресурсов. Но вообще, запрос, который вы написали изначально, намного сложнее, чем мои к нему "доработки".