Unexpected database output when using INNER JOIN

I have the following SQL query

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.

For example:

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?

Thanks in advance

Answer

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

Will get you only the rooms that are found in the tables RAUM, RAZUORDNUNG and RAUMATTRIBUTE tables, removing these INNER JOINs will get you all the rooms from the RAUM table that satisfy your condition, check these pages for more details about JOINs:

Advertisement

Answer