Блог

Для решения этой проблемы рассмотрим следующие 3 таблицы:

 Event
id (pk)
title

Event_Category
event_id (pk, fk)
category_id (pk, fk)

Category
id (pk)
description
  

Довольно тривиально, я думаю … 🙂 Каждое событие может попасть в ноль или более категорий, всего существует 4 категории.
В моем приложении я хочу просматривать и редактировать категории для определенного события. Графически событие будет показано вместе со ВСЕМИ категориями и флажком, указывающим, попадает ли событие в категорию. Изменение и сохранение выбора приведет к изменению промежуточной таблицы Event_Category.
Но сначала: как выбрать это для конкретного события? Нужный мне запрос фактически всегда будет возвращать 4 строки, количество присутствующих категорий.

Далее возвращаются только записи для категорий, в которые попадает событие с идентификатором=11. Эксперименты с внешними объединениями не дали больше строк в результате.

 SELECT e.id, c.omschrijving 
FROM Event e
  INNER JOIN Event_Categorie ec ON e.id = ec.event_id
  INNER JOIN Categorie c ON c.id = ec.categorie_id
WHERE e.id = 11
  

Или мне следует начать с таблицы категорий в запросе? Надеюсь на некоторые подсказки 🙂
TIA, Klaas

ОБНОВЛЕНИЕ: Да, я сделал, но до сих пор не нашел ответа. Но я упростил проблему, исключив таблицу событий из запроса, потому что эта таблица используется только для просмотра описаний событий.

 SELECT * from Categorie c LEFT JOIN Event_Categorie ec ON c.id = ec.categorie_id WHERE ec.event_id = 11;
  

Упрощенный запрос из 2 таблиц использует только таблицу поиска и таблицу ссылок, но по-прежнему возвращает только 2 строки вместо общего количества 4 строк в таблице категорий.
Я предполагаю, что предложение WHERE применяется после объединения, поэтому строки, не присоединенные к таблице ссылок, исключаются. В моем приложении я решил проблемы с помощью подзапроса, но я все еще хотел бы знать, какое решение является лучшим.

Вам нужен список всех категорий, а также информация о том, есть ли эта категория в списке категорий вашего события.

Итак, вы можете сделать:

 SELECT 
    * 
FROM 
    Category 
    LEFT JOIN Event_Category ON category_id = id 
WHERE 
    event_id = 11
  

и столбец event_id будет равен нулю для категорий, которые не являются частью вашего события.

Вы также можете создать столбец (с именем has_category ниже), который вы будете использовать, чтобы увидеть, имеет ли событие эту категорию, вместо сравнения с NULL:

 SELECT 
    *,
    event_id IS NOT NULL AS has_category
FROM 
    Category 
    LEFT JOIN Event_Category ON category_id = id 
WHERE 
    event_id = 11
  

РЕДАКТИРОВАТЬ: Похоже, это именно то, что вы говорите, что делаете при редактировании. Я протестировал это, и это кажется правильным. Вы уверены, что выполняете этот запрос, и что строки с NULL каким-либо образом не игнорируются?

Запрос

 SELECT * FROM Categorie;
  

возвращает 4 строки:

  ---- -------------- ------------------------------------- -------------------------------------- 
| id | omschrijving | afbeelding                          | afbeelding_klein                     |
 ---- -------------- ------------------------------------- -------------------------------------- 
|  1 | Creatief     | images/categorieen/creatief420k.jpg | images/categorieen/creatief190k.jpg  |
|  2 | Sportief     | images/categorieen/sportief420k.jpg | images/categorieen/sportief190kr.jpg |
|  4 | Culinair     | images/categorieen/culinair420k.jpg | images/categorieen/culinair190k.jpg  |
|  5 | Spirit       | images/categorieen/spirit420k.jpg   | images/categorieen/spirit190k.jpg    |
 ---- -------------- ------------------------------------- -------------------------------------- 
4 rows in set (0.00 sec)
  

НО:
Запрос

 SELECT * 
FROM Categorie 
LEFT JOIN Event_Categorie ON categorie_id = id 
WHERE event_id = 11;
  

возвращает 2 строки:

  ---- -------------- ------------------------------------- ------------------------------------- ---------- -------------- 
| id | omschrijving | afbeelding                          | afbeelding_klein                    | event_id | categorie_id |
 ---- -------------- ------------------------------------- ------------------------------------- ---------- -------------- 
|  1 | Creatief     | images/categorieen/creatief420k.jpg | images/categorieen/creatief190k.jpg |       11 |            1 |
|  4 | Culinair     | images/categorieen/culinair420k.jpg | images/categorieen/culinair190k.jpg |       11 |            4 |
 ---- -------------- ------------------------------------- ------------------------------------- ---------- -------------- 
2 rows in set (0.00 sec)
  

Поэтому мне все еще нужен подзапрос… и ЛЕВОЕ СОЕДИНЕНИЕ неэффективно для отображения всех строк таблицы категорий, независимо от того, есть ли совпадение с таблицей ссылок.

Однако этот запрос выполняет то, что я хочу, чтобы он делал:

 SELECT * 
FROM Categorie c 
LEFT JOIN (SELECT * FROM Event_Categorie ec WHERE ec.event_id = 11 ) AS subselect ON  subselect.categorie_id = c.id;
  

Результат:

  ---- -------------- ------------------------------------- -------------------------------------- ---------- -------------- 
| id | omschrijving | afbeelding                          | afbeelding_klein                     | event_id | categorie_id |
 ---- -------------- ------------------------------------- -------------------------------------- ---------- -------------- 
|  1 | Creatief     | images/categorieen/creatief420k.jpg | images/categorieen/creatief190k.jpg  |       11 |            1 |
|  2 | Sportief     | images/categorieen/sportief420k.jpg | images/categorieen/sportief190kr.jpg |     NULL |         NULL |
|  4 | Culinair     | images/categorieen/culinair420k.jpg | images/categorieen/culinair190k.jpg  |       11 |            4 |
|  5 | Spirit       | images/categorieen/spirit420k.jpg   | images/categorieen/spirit190k.jpg    |     NULL |         NULL |
 ---- -------------- ------------------------------------- -------------------------------------- ---------- -------------- 
4 rows in set (0.00 sec)
  

Проблема в том, что вы отфильтровали результаты по идентификатору события. Как вы можете видеть в своих результатах, в двух категориях (Sportief и Spirit) нет событий. Таким образом, правильный оператор SQL (с использованием синтаксиса SQL Server; может потребоваться некоторый перевод) является:

 SELECT * 
FROM Categorie 
LEFT JOIN Event_Categorie ON categorie_id = id 
WHERE (event_id IS NULL) OR (event_id = 11);
  

Наконец-то я нашел правильный запрос, никакого дополнительного выбора не требуется. Но предложение WHERE работает после объединения и, следовательно, больше не является частью объединения. Решение заключается в расширении предложения ON дополнительным условием. Теперь все 4 строки возвращаются с NULL для несоответствующих категорий!

 SELECT * 
FROM Categorie  
LEFT JOIN Event_Categorie ON categorie_id = id AND event_id = 11;
  

Итак, суть в том, что добавление дополнительного условия в предложение ON имеет иной эффект, чем фильтрация строк по тому же условию в предложении WHERE!