Search code examples
sqlsql-serveriif

Select a certain item unless is null in SQL Server

I'm trying to pull a certain item unless the first item is null.

SELECT  
   FM.FileNumber
   ,FM.ClientsFileNumber AS 'Clients File Number'
   ,FM.FileID AS FileID
   ,S.Name AS Status
   ,FA.ReceivedDate AS 'Contract Fr Seller' --550, 238
   ,FA1.ReceivedDate AS 'Deed To Seller'    --266, 561
   ,FA2.ReceivedDate AS 'Deed Fr Seller'    --40, 629
   ,FA3.ReceivedDate AS 'HUDTo Seller'                     --269, 587
   ,FA4.ReceivedDate AS 'HUDFr Seller'                      --274, 637
   ,FA5.ReceivedDate AS 'Sent HUD to Buyer'          --268
   ,FA6.ReceivedDate AS 'Receive Buyer Signed Docs'          --273
   ,FA7.ReceivedDate AS 'Receive Buyer Funds'                      --119
   ,FA8.ReceivedDate AS 'Closing Completed'                          --272
   ,FA9.ReceivedDate AS 'FC Deed' --344
   -- ,n.Body as Notes
FROM   
   FileMain FM
LEFT OUTER JOIN      
   FileActions FA ON FA.FileID = FM.FileID AND FA.ActionDefID IN (238,550)
                     AND FA.Live = 1 AND FA.ReceiveCoordinatorTypeID = 2
                    --AND FA.ReceivedDate > '2013-03-01'
                    AND FA.ReceivedDate IS NOT NULL
                    AND FA.ActionDefID IS NOT NULL

Basically I am trying to get it to select FA.ActioNDefID = 230 unless that is NULL, then I want it to pull FA.ActionDefID = 550, and the same for the other multiple selections.

Below is what I am currently getting, now the later one will ALWAYS be 238 unless it is null, basically I am only wanting to get 1 of those File Numbers, if 238 is null, then pull 550 only.

enter image description here

Solution

  • I would suggest rewrite you query like this, should be more readable

    SELECT
   ...
  , [Contract Fr Seller] = (SELECT TOP 1 FA.ReceivedDate 
                     FROM FileActions FA 
                     WHERE FA.FileID = FM.FileID
                          AND  FA.ActionDefID IN (238,550)
                          AND FA.Live = 1
                          AND FA.ReceiveCoordinatorTypeID = 2 
                     ORDER BY FA.ActionDefID  -- This will make sure 238 on the top if exist
                      )                                 
FROM FileMain FM
...