Nesting sql queries inside case

Question

Hi guys,

I am trying to write a SQL query where I am joining two tables and retrieving a few columns. Once this is done, based on a two fields (source table enum, id in the corresponding source table), I need to retrieve a value matching the id in the source table.

The issue is that I cannot join all the source tables, and want to do something like this:

Select 
  X.Col1,
  X.Col2,
  Y.Col1,
  Y.Col2,
  CASE
    WHEN Y.TableID = 4 THEN Select t4.Col1 FROM TableFour t4 WHERE t4.id = Y.FileID
    WHEN Y.TableID = 5 THEN Select t5.Col4 FROM TableFive t5 WHERE t5.id = Y.FileID
  END
FROM Table X INNER JOIN Table Y ON X.ID = Y.XID

I can guarantee the value being retrieved from all the source tables will be the same (i.e. nvarchar).

Nesting SQL queries inside the CASE statement doesn't seem to work, I am throwing it you guys. Any ideas with this problem?

Hope I explained the question adequately. If you are unsure, make a comment so I can clarify it.

Cheers in advance!

Answer 1

I would probably write a function for this:

CREATE FUNCTION dbo.GetVal
(@tblNum int, @FileID int)
RETURNS nvarchar(255)
AS
BEGIN
  Declare @return nvarchar(255);
  if @tblNum = 4 then
    Select @return=t4.Col1 FROM TableFour t4 WHERE t4.id = @FileID;
  else
    if @tblNum =5 
      Select @return=t5.Col4 FROM TableFive t5 WHERE t5.id = @FileID;
  return @return
END;

Then your Select just looks like:

Select 
  X.Col1,
  X.Col2,
  Y.Col1,
  Y.Col2,
  dbo.GetVal(Y.TableID, Y.FileID) 
FROM Table X INNER JOIN Table Y ON X.ID = Y.XID

Answer 2

Use brackets to encapsulate the SELECT within the CASE expression:

Select X.Col1,
       X.Col2,
       Y.Col1,
       Y.Col2,
       CASE
         WHEN Y.TableID = 4 THEN (Select t4.Col1 FROM TableFour t4 WHERE t4.id = Y.FileID) 
         WHEN Y.TableID = 5 THEN (Select t5.Col4 FROM TableFive t5 WHERE t5.id = Y.FileID)
       END
  FROM Table X 
  JOIN Table Y ON X.ID = Y.XID

...but I'd like to stress that this is a poor design choice. You could use LEFT JOINs instead:

   Select X.Col1,
          X.Col2,
          Y.Col1,
          Y.Col2,
          CASE
            WHEN Y.TableID = 4 AND t4.Col1 IS NOT NULL THEN t4.Col1 
            WHEN Y.TableID = 5 AND t5.Col4 IS NOT NULL THEN t5.Col4
          END
     FROM Table X 
     JOIN Table Y ON X.ID = Y.XID
LEFT JOIN TABLEFOUR t4 ON t4.id = y.fileid
LEFT JOIN TABLEFIVE t5 ON t5.id = y.fileid

Answer 3

I don't understand what do you mean by "cannot join all the source tables", can you elaborate? I would use outer joins:

Select 
  X.Col1,
  X.Col2,
  Y.Col1,
  Y.Col2,
  COALESCE(t4.Col1 ,t5.Col4)
  END
FROM Table X INNER JOIN Table Y ON X.ID = Y.XID
LEFT OUTER JOIN TableFour t4 ON t4.id = Y.FileID AND Y.TableID = 4
LEFT OUTER JOIN TableFive t5 ON t5.id = Y.FileID AND Y.TableID = 5