Search code examples
sqlsql-servercoalesce

SQL Server: more than two tables for coalesce function


I need help, I managed to get successful query for two tables, but I got error when I am trying to do more than two table. please help me.

This is the SQL:

select 
    coalesce(a.LOT_ID, b.LOT_ID,c.LOT_ID,d.LOT_ID) as LotId, 
    coalesce(a.CheckIn, b.CheckIn,c.CheckIn, d.CheckIn) as CheckIn,
    coalesce(a.CheckOut, b.CheckOut,c.CheckOut, d.CheckOut) as CheckOut,
    coalesce(a.StatusDesc, b.StatusDesc,c.StatusDesc, .StatusDesc) as StatusDesc
from 
    LOT_LOC_BOND a, LOT_LOC_IEBT b,LOT_LOC_MBT c,LOT_LOC_SEAL d
where   
    a.LOT_ID = b.LOT_ID,
    AND c.LOT_ID = d.LOT_ID

and the error stated

Incorrect syntax near ','.


Solution

  • Missing alias name at the end

    coalesce(a.StatusDesc, b.StatusDesc,c.StatusDesc, .StatusDesc) --d is missing 
    

    start using INNER JOIN syntax to join tables

    SELECT COALESCE(nullif(a.LOT_ID,''), nullif(b.LOT_ID,''), nullif(c.LOT_ID,''), nullif(d.LOT_ID,'')) AS LotId,
           COALESCE(a.CheckIn, b.CheckIn, c.CheckIn, d.CheckIn) AS CheckIn,
           COALESCE(a.CheckOut, b.CheckOut, c.CheckOut, d.CheckOut) AS CheckOut,
           COALESCE(a.StatusDesc, b.StatusDesc, c.StatusDesc, d.StatusDesc) AS StatusDesc --missing alias name d  
    FROM   LOT_LOC_BOND a
           INNER JOIN LOT_LOC_IEBT b
                   ON a.LOT_ID = b.LOT_ID
           INNER JOIN LOT_LOC_MBT c
                   ON a.LOT_ID = c.LOT_ID
           INNER JOIN LOT_LOC_SEAL d
                   ON c.LOT_ID = d.LOT_ID