使用INNER JOIN时,意外的数据库输出 [英] Unexpected database output when using INNER JOIN
问题描述
我有以下SQL查询
SELECT
r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID,
ra.BEZEICHNUNG AS raumBEZEICHNUNG, ra.ID AS raumID
FROM
RAUM r
INNER JOIN RAZUORDNUNG rz ON rz.RAUM_ID = r.ID
INNER JOIN RAUMATTRIBUTE ra ON rz.RAUMATTRIBUTE_ID = ra.ID
WHERE
RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID)
AND STADT_ID = ISNULL(@Stadt_ID, STADT_ID)
AND GEBAEUDE_ID = ISNULL(@Gebaeude_ID, GEBAEUDE_ID)
AND REGION_ID = ISNULL(@Region_ID, REGION_ID)
AND RAUMATTRIBUTE_ID = ISNULL(@Raumattribute_ID, RAUMATTRIBUTE_ID)
不过,我觉得这事是不对的。
But I think that something is wrong with that.
例如:
如果我把三
在浏览器中的 RAUMKLASSE_ID
文本框并调用我的方法返回只有一个房间。但也有6个房间的ID。奇怪的是,如果我删除了两个 INNER JOIN
和我的 SELECT
,这样第二行:
If I put three
in the RAUMKLASSE_ID
textfield in the browser and invoke my method it returns only one room. But there are six rooms with that ID. The strange thing is, that if I remove the two INNER JOIN
and the second line of my SELECT
, like this:
SELECT
r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID
FROM
RAUM r
WHERE
RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID)
AND STADT_ID = ISNULL(@Stadt_ID, STADT_ID)
AND GEBAEUDE_ID = ISNULL(@Gebaeude_ID, GEBAEUDE_ID)
AND REGION_ID = ISNULL(@Region_ID, REGION_ID)
AND RAUMATTRIBUTE_ID = ISNULL(@Raumattribute_ID, RAUMATTRIBUTE_ID)
它返回的六间房,这是正确的。我不知道这个问题是我的查询内容。也许有人可以帮我吗?
it is returning the six rooms, which is correct. I don't know what the problem is with my query. Maybe someone can help me with that?
在此先感谢
推荐答案
这是预期的行为,因为:
This is the expected behaviour, since:
FROM RAUM r
INNER JOIN RAZUORDNUNG rz ON rz.RAUM_ID = r.ID
INNER JOIN RAUMATTRIBUTE ra ON rz.RAUMATTRIBUTE_ID = ra.ID
会得到你只有在表中发现的客房 RAUM
, RAZUORDNUNG
和 RAUMATTRIBUTE
表中,除去这些 INNER JOIN
旨意让你所有这一切满足您的条件 RAUM
表中的房间,检查这些页面了解更多详细的加入
取值:
Will get you only the rooms that are found in the tables RAUM
, RAZUORDNUNG
and RAUMATTRIBUTE
tables, removing these INNER JOIN
s will get you all the rooms from the RAUM
table that satisfy your condition, check these pages for more details about JOIN
s:
- A visual explanation for
JOIN
s. - Wikipedia article about
JOIN
s
这篇关于使用INNER JOIN时,意外的数据库输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!