Post reply

JOIN with CASE statement (multiple columns)

  • October 12, 2021 at 12:27 am

    #3939627

    I read a couple of explanations online, but it is still hard for me to understand how to join two tables on multiple columns. If someone could give me a hand, I would greatly appreciate.

    I have 2 tables

    FCSTPERFSTATIC table - has a column DMDUNIT (code of item). I need to join it to a different tables (CKB_Snapshot). This table has two columns: 1) UPC (item number) and 2) Prepack.

    Prepack column sometimes has values inside and sometimes it has NULL values. So I want to join DMDUNIT from FCSTPERFSTATIC to PREPACK column of CKB_Snapshot, however when it has NULL values I want to join it on a different column namely UPC column.

     

      SELECT f.DMDUNIT, c.PREPACK, c.UPC, c.REPLENTYPE
     
      FROM [FCSTPERFSTATIC] f
      LEFT OUTER JOIN [UDT_CKB_SNAPSHOT] c
      ON f.DMDUNIT=c.PREPACK
     
      GROUP BY f.DMDUNIT, c.PREPACK, c.UPC, c.REPLENTYPE

     

     

    • This topic was modified 3 years, 1 month ago by  JeremyU.

  • October 12, 2021 at 1:15 am

    #3939633

    If someone may know, please let me know.

    • This reply was modified 3 years, 1 month ago by  JeremyU.

  • October 12, 2021 at 5:44 am

    #3939668

    one way of doing it

    SELECT f.DMDUNIT, c.PREPACK, c.UPC, c.REPLENTYPE
     
      FROM [FCSTPERFSTATIC] f
      LEFT OUTER JOIN [UDT_CKB_SNAPSHOT] c
      ON f.DMDUNIT=c.PREPACK
      or (c.PREPACK is null and f.DMDUNIT = c.UPC)
     
      GROUP BY f.DMDUNIT, c.PREPACK, c.UPC, c.REPLENTYPE

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply