以n:m关系联接3个表，要查看不匹配的行 [英] Joining 3 tables with n:m relationship, want to see nonmatching rows

问题描述

对于此问题，请考虑以下3个表:

For this problem, consider the following 3 tables:

Event
id (pk)
title

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

Category
id (pk)
description

我猜很简单... :)每个事件可以分为零个或多个类别，总共有4个类别. 在我的应用程序中，我想查看和编辑特定事件的类别.以图形方式显示事件，所有类别和一个复选框，指示事件是否属于该类别.更改并保存选择将导致中间表Event_Category的修改. 但首先:如何为特定事件选择此项?实际上，我需要的查询总是返回4行，即当前存在的类别数.

Pretty trivial I guess... :) Each event can fall into zero or more categories, in total there are 4 categories. In my application, I want to view and edit the categories for a specific event. Graphically, the event will be shown together with ALL categories and a checkbox indicating whether the event falls into the category. Changing and saving the choice will result in modifocation of the intermediate table Event_Category. But first: how to select this for a specific event? The query I need will in fact always return 4 rows, the number of categories present.

以下仅返回id = 11的事件所属类别的条目.对外部联接进行试验并没有在结果中提供更多的行.

Following returns only the entries for the categories the event with id=11 falls into. Experimenting with outer joins did not give more rows in the result.

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，克拉斯

Or should I start with the Category table in the query? Hope for some hints :) TIA, Klaas

更新: 是的，我做了，但仍然没有找到答案.但是我通过从查询中省略事件表来简化了此问题，因为该表仅用于查看事件描述.

UPDATE: Yes I did but still have not found the answer. But I have simplified the issue by omitting the Event table from the query because this table is only used to view the Event descriptions.

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

简化的2表查询仅使用查找表和链接表，但仍仅返回2行，而不返回Categorie表中的4行. 我的猜测是在联接之后应用了WHERE子句，因此排除了未联接到链接表的行.在我的应用程序中，我通过使用子查询解决了问题，但我仍然想知道什么是最佳解决方案.

The simplified 2-table query only uses the lookup table and the link table but still returns only 2 rows instead of the total of 4 rows in the Categorie table. My guess would be that the WHERE clause is applied after the joining, so the rows not joined to the link table are excluded. In my application I solved the issues by using a subquery but I still would like to know what is the best solution.

推荐答案

最后，我找到了正确的查询，没有必要进行子选择.但是WHERE子句在连接后起作用，因此不再是连接的一部分.解决方案是使用额外条件扩展ON子句.现在，对于不匹配的类别，所有4行都返回NULL！

Finally I found the right query, no subselect is necessary. But the WHERE clause works after the joining and therefore is no part of the join anymore. THe solution is extending the ON clause with an extra condition. Now all 4 rows are returned with NULL for the non-matching Categories!

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

因此，最重要的是，在ON子句中添加额外条件的效果与在WHERE子句中按相同条件过滤掉行的效果不同！

So the bottom line is that putting an extra condition in the ON clause has different effect than filtering out rows by the same condition in the WHERE clause!

这篇关于以n:m关系联接3个表，要查看不匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！