Join Three Tables with get value Sitename from Site

asked 2021-11-24T07:14:42.627+00:00
Riyn Pratama 41 Reputation points

I'm trying join Three tables:

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,485 questions
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2021-11-24T09:46:57.747+00:00
    EchoLiu-MSFT 14,416 Reputation points

    Please check:

    ;WITH cte as  
    (SELECT t.CNTRCTHID AS CONTRACTID,    
     t.CNTRCTHIDEXT AS CONTRACTNO,    
     t.CNTRCTHSDATE AS STARTDATE,    
     t.CNTRCTHEDATE AS ENDDATE,    
    t.CNTRCTHCONFID AS CONFIG,    
     t.CNTRCTHSONO AS SONO,    
    h.CNTRCTHHISTLASTLOC AS LASTLOCATION  
    FROM #KDSCMSCNTRCTH t   
    left join #KDSCMSCNTRCTHHIST h   
    ON h.CNTRCTHHISTLNUM = t.CNTRCTHLNUM and h.CNTRCTHHISTCOMP = t.CNTRCTHCOMP    
    left join #KDSCMSCNTRCTHHIST h2   
    ON h2.CNTRCTHHISTLNUM = t.CNTRCTHLNUM and h2.CNTRCTHHISTCOMP = t.CNTRCTHCOMP   
    and h2.CNTRCTHHISTSDATE > h.CNTRCTHHISTSDATE    
    where t.CNTRCTHCOMP = :Company and h2.CNTRCTHHISTID is null)  
      
    SELECT c.*,CONCAT_WS(' 'C.LASTLOCATION,k.SITESITENAME) LASTLOCATION   
    FROM cte c  
    JOIN KDSCMSSITE k ON c.LASTLOCATION=k.SITESITE)  
    

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards,
    Echo


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    No comments