Join Three Tables with get value Sitename from Site

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

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.
12,705 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

1 answer

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

    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.

    0 comments