SELECT table1.column1,

'99',

left(table1.column2, 2),

max(table1.column3),

'Yes',

'Test'

FROM table1

WHERE NOT EXISTS ( SELECT 1

FROM TABLE2 WHERE ltrim(rtrim(table2.column1)) + ltrim(rtrim(left(table2.column2, 2)))=

ltrim(rtrim(table1.column1)) + ltrim(rtrim(left(table1.column2, 2)))

)

AND table1.column4 = 'Yes'

GROUP BY table1.column1, Left(table1.column2, 2)