I have two tables:
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 |
So any test have prep materials. And some test’s have same material.
I want to delete all material’s for 2023 tests. But 2024 test’s may have same path.
So i need to pick all prep materials for 2023 tests and exclude from them all path’s which have dependencies in 2024 tests.
I have tried the following, but there are too many results to know if the command was executed correctly.
<code>SELECT `test_prep_materials`.`PATH`, `tests`.`id`, `tests`.`year`, `tests`.`Name_t`
from `test_prep_materials`
JOIN `tests` ON `test_prep_materials`.`tId` = `tests`.`id`
WHERE `tests`.`year` = 2023
EXCEPT
SELECT `test_prep_materials`.`PATH`, `tests`.`id`, `tests`.`year`, `tests`.`Name_t`
from `test_prep_materials`
JOIN `tests` ON `test_prep_materials`.`tId` = `tests`.`id`
WHERE `tests`.`year` <> 2023
</code>
<code>SELECT `test_prep_materials`.`PATH`, `tests`.`id`, `tests`.`year`, `tests`.`Name_t`
from `test_prep_materials`
JOIN `tests` ON `test_prep_materials`.`tId` = `tests`.`id`
WHERE `tests`.`year` = 2023
EXCEPT
SELECT `test_prep_materials`.`PATH`, `tests`.`id`, `tests`.`year`, `tests`.`Name_t`
from `test_prep_materials`
JOIN `tests` ON `test_prep_materials`.`tId` = `tests`.`id`
WHERE `tests`.`year` <> 2023
</code>
SELECT `test_prep_materials`.`PATH`, `tests`.`id`, `tests`.`year`, `tests`.`Name_t`
from `test_prep_materials`
JOIN `tests` ON `test_prep_materials`.`tId` = `tests`.`id`
WHERE `tests`.`year` = 2023
EXCEPT
SELECT `test_prep_materials`.`PATH`, `tests`.`id`, `tests`.`year`, `tests`.`Name_t`
from `test_prep_materials`
JOIN `tests` ON `test_prep_materials`.`tId` = `tests`.`id`
WHERE `tests`.`year` <> 2023
Can somebody help me understand if my Query right or i am missing something?