SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64) 
	Oct 18 2018 23:11:05 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)




CREATE TABLE Table1
    ([ID] varchar(6), [t1col2] varchar(6), [t1col3] varchar(7))
;
    
INSERT INTO Table1
    ([ID], [t1col2], [t1col3])
VALUES
    ('123', 'Fname1', 'Lname1'),
    ('456', 'Fname2', 'Lname2'),
    ('789', 'Fname3', 'LnameAA')
;



CREATE TABLE Table2
    ([ID] varchar(5), [t2col2] varchar(6), [t2col3] varchar(6), [t2col4] varchar(7))
;
    
INSERT INTO Table2
    ([ID], [t2col2], [t2col3], [t2col4])
VALUES
    ('122', 'Fname1', 'Lname1', 'String1'),
    ('466', 'Fname2', 'Lname2', 'String2'),
    ('789', 'Fname3', 'Lname3', 'String3')
;




CREATE TABLE Table3
    ([ID] int, [t3col2] varchar(6))
;
    
INSERT INTO Table3
    ([ID], [t3col2])
8 rows affected

SELECT T1.ID , T1COL2, T2COL3, T2COL4, T3COL2
FROM TABLE2 AS T2
INNER JOIN TABLE1 AS T1 
  ON T2.ID = T1.ID AND T1.T1COL3 LIKE '%AA' 
INNER JOIN TABLE3 AS T3
  ON T3.ID = T2.ID


ID
T1COL2
T2COL3
T2COL4
T3COL2


789
Fname3
Lname3
String3
asdfgh