outer join between tables

Question

There is a table table1 having a column hId. This can contain numerical also, including also 0 and null. After some joining relation, each table1.hId have a full name, but only if hId !=0 or null

Then table2.Hpid = table1.hId -- but only if hId is not null or != 0

After this, table2.exId = table3.Id and, finally, table3.lId = table4.Id

What I've tried

SELECT
  (SELECT table4.fullName
   FROM table4
   WHERE table4.Id = table3.lId)
FROM table1
JOIN table2 ON table1.hId = table2.hpId
JOIN table3 ON table3.Id = table2.exId

But in this way I get only the rows where table1.id = table2.hId. I believe I have to use a case statement ( and when table.hId = 0 or null => then print 'error' ) , but how can I achieve this?

Answer 1

i can't understand the goal but try to go with left joins:

SELECT ISNULL(table4.Id, 'ERROR') AS Table4Id
       , table4.fullName
FROM table1
LEFT JOIN table2 ON table1.holidayparkid2 = table2.Id
LEFT JOIN table3 ON ed.Id = table2.exId <-- what's ed.Id?
LEFT JOIN table3 ON table4.Id = table3.lId;

Answer 2

SELECT CASE WHEN (table.hId = 0 OR table.hId IS NULL) THEN 'ERROR'
            ELESE table4.fullName
       END AS fullName
FROM table1
JOIN table2 ON table1.holidayparkid2 = table2.Id
JOIN table3 ON ed.Id = table2.exId
JOIN table4 ON table4.Id = table3.lId
--WHERE table1.hId!=0 AND table1.hId IS NOT NULL  --not need with case