JimmyJimm JimmyJimm - 6 months ago 8
SQL Question

Extract proper values using 3 tables, when null/not null

Have three tables as follows:

tbSubSubKategorie:

ID Name
1 PodPodKategoria_1
2 PodPodKategoria_2
3 PodPodKategoria_3
4 PodPodKategoria_4
6 PodPodKategoria_5


tbSection

Id Name
2 Design
3 Wichtigste Eigenschaften der Stoffe
4 Pflegehinweis
5 Funktionen
6 Sitzkomfort
7 Abmessungen im Detail
8 Versand & Montage
9 Lieferumfang


tbSection_SubSubKategorie

Id FK_Section_ID FK_SubSubKategorie
2 2 2
5 6 2
7 2 3
8 7 3
10 5 2
11 5 3
13 8 NULL
15 3 2


In model there could be that one section could be associated with multi diffrent subsubkategories or second case when section is not associated with any subsubkategorie then for this section will be always one record with section id and null value in FK_SubSubKategorie field.

What i would like to achieve now is to get information what are the connections from the table tbSection_SubSubKategorie. I would like to get information:


tbSection_SubSubKategorie.Id, tbSectionId, tbSection.Name,
FK_SubSubKategorie.Name


I have problem when section is associated with null for subsubkategorie.

My current query is built like this:

SELECT CASE WHEN section_subsubkategorie.FK_SubSubKategorie IS NULL THEN 'section does not contain subsubkategorie' ELSE convert(varchar(10), section_subsubkategorie.FK_SubSubKategorie, 121) END AS IDX, *
FROM tbSection as section
INNER JOIN tbSection_SubSubKategorie as section_subsubkategorie ON section.Id = section_subsubkategorie.FK_Section_ID
INNER JOIN tbSubSubKategorie as subkategorie
ON section_subsubkategorie.FK_SubSubKategorie = subkategorie.Id OR section_subsubkategorie.FK_SubSubKategorie IS NULL


but result is incorrect:

enter image description here

As you se for section Id 8 i see 4 rows, and should be only one... I even tried to use CASE but still its not THIS. Could you help? Hope you get my point.

Tables creation code

Answer

Replace your last INNER JOIN with a LEFT JOIN and remove the OR clause:

SELECT CASE WHEN section_subsubkategorie.FK_SubSubKategorie IS NULL THEN 'section does not contain subsubkategorie' ELSE convert(varchar(10), section_subsubkategorie.FK_SubSubKategorie, 121)  END AS IDX, *
        FROM tbSection as section
                INNER JOIN tbSection_SubSubKategorie as section_subsubkategorie ON section.Id = section_subsubkategorie.FK_Section_ID
                LEFT JOIN tbSubSubKategorie as subkategorie 
                ON section_subsubkategorie.FK_SubSubKategorie = subkategorie.Id

Right now you're telling it to join only if the two ids match (which they don't since the second is null), or to join a null to every id separately, which is causing the duplicate lines for each of the first 6 ids.

A LEFT JOIN will keep the original line, and simply add null to the FK_SubSubKategorie field, which will also allow your case statement to work properly for the IDX field.

Comments