Include records that has a null value on INNER JOIN SQL Server query over a tree table

Question

I'm querying over a table that has nested references to itself, and I need to get records has a null reference on the INNER JOIN value which i'm using. In other words, the tree table records may not reach the same depth and I need to get all of them.

The query I'm using atm is:

SELECT DISTINCT <valuesThatINeed>
FROM atable foo
   INNER JOIN treetable bar ON foo.id_bar = bar.id
   INNER JOIN treetable bar2 ON bar.id_bar = bar2.id
   INNER JOIN treetable bar3 ON bar2.id_bar = bar3.id
WHERE
   <constraints>

I've read that I can extend the INNER JOIN condition by adding an OR statement asking if bar.id_bar IS NULL, but I cannot reach further levels if I do that:

SELECT DISTINCT <valuesThatINeed>
FROM atable foo
   INNER JOIN treetable bar ON foo.id_bar = bar.id 
   INNER JOIN treetable bar2 ON bar.id_bar = bar2.id OR (bar.id_bar IS NULL) 
   INNER JOIN treetable bar3 ON bar2.id_bar = bar3.id
WHERE
   <constraints>

Answer 1

Change the INNER JOIN to a LEFT JOIN:

SELECT DISTINCT <valuesThatINeed>
, CASE 
     WHEN barN.id IS NULL 
         THEN 'Your previous level with BarID = ' 
               + CAST(barN-1.id AS NVARCHAR(MAX)) 
               + ' is the deepest level'
      ELSE ''
  END
FROM atable foo
   LEFT JOIN treetable bar ON foo.id_bar = bar.id 
   LEFT JOIN treetable bar2 ON bar.id_bar = bar2.id OR (bar.id_bar IS NULL) 
   LEFT JOIN treetable bar3 ON bar2.id_bar = bar3.id
   ...
   LEFT JOIN treetable barN ON barN-1.id_bar = barN.id
WHERE
   <constraints>

You can continue making LEFT joins until you reach a depth where your barN.id IS NULL, which will mean that you have reached the deepest level at N-1.

But if you're trying to make a hierarchy, then this is not the scalable way to go. Do a web-search for recursive CTEs (here is a possible hint which you can try to adapt to your situation).

---

.