SQL Server Conditional Joins

Question

I've three tables Table1 Table2 Table3. I've to perform some operations on them and store the resultant in Table4

Table1:

ID       t1col2     t1col3  
``````   ``````     ``````
123      Fname1     Lname1
456      Fname2     Lname2  
789      Fname3     LnameAA  

Table2:

ID        t2col2     t2col3     t2col4
`````     ``````     ``````     ``````
122       Fname1     Lname1     String1
466       Fname2     Lname2     String2
789       Fname3     Lname3     String3  

Table3:

ID       t3col2       
``````   ``````      
122      querty     
789      asdfgh 

How can I perform conditional joins to check for following conditions:

• Search for a substring AA in t1col3.
• If found, replace t1col3 value from Table1 with t2col3 value from Table2 only when Table1 ID and Table2 ID are equal.
• From the above result, search for matching ID in Table3
• If found, display the content in Table4 as mentioned below.

Expected output:

Table4:

ID         t1col2     t2col3     t2col4     t3col2  
```````    ```````    ```````    ```````    ```````  
789        Fname3     Lname3     String3    asdfgh

Answer 1

First insert them in Table4.

Then correct Table1 based on Table4.

DELETE FROM Table4;

INSERT INTO Table4 (ID, t1col2, t2col3, t2col4, t3col2)
SELECT t2.ID, t1.t1col2, t2.t2col3, t2.t2col4, t3.t3col2
FROM Table2 t2
JOIN Table1 t1 ON t1.ID = t2.ID AND t1.t1col3 LIKE '%AA%'
JOIN Table3 t3 ON t3.ID = t1.ID;

UPDATE t1
SET t1col3 = t4.t2col3
FROM Table1 t1
JOIN Table4 t4 ON t4.ID = t1.ID
WHERE t1.t1col3 != t4.t2col3;

SELECT * 
FROM Table4 
ORDER BY ID;

Test on db<>fiddle here

Result:

ID      t1col2      t2col3      t2col4      t3col2
----    --------    --------    --------    --------
789     Fname3      Lname3      String3     asdfgh