0

I am using Left Joins on a self-referenced table to see if an item has a parentID and then fetch the corresponding itemName of the parent. The parentID can be an integer or NULL.

My query works as intended. I am just wondering if there is a way I can return a placeholder / text (e.g. "---") as otherwise it just returns nothing for this specific part of the query. Maybe by using a Case statement ?

The parts in question are E.itemName and D.itemName as both are only available if D.parentID resp. A.parentID are not NULL.

My procedure (shortened):

SELECT      A.itemName,
            B.lastUpdate,
            B.modTime,
            B.modBy,
            E.itemName AS levelMain,
            D.itemName AS levelSub
FROM        MOC_Links A
LEFT JOIN   MOC_Log B
ON          B.itemID = A.itemID
LEFT JOIN   MOC_Links D
ON          D.itemID = A.parentID
LEFT JOIN   MOC_Links E
ON          E.itemID = D.parentID
WHERE       A.itemName LIKE '%'+@searchTerm+'%'
ORDER BY    itemName, levelMain
FOR XML PATH('results'), ELEMENTS, TYPE, ROOT('ranks')
halfer
  • 19,824
  • 17
  • 99
  • 186
user2571510
  • 11,167
  • 39
  • 92
  • 138

2 Answers2

2

I think you are looking for coalesce():

coalesce(E.itemName, '---') as levelMain
coalesce(D.itemName, '---') as levelSub
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You could use Isnull, e.g. ISNULL(E.itemName, '----')

Donal
  • 31,121
  • 10
  • 63
  • 72