carlosaamaral (9/11/2012)
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)
I believe the only reason to use concatenation was IN construction.
NOT EXISTS does not need that:
SELECT table1.column1,
'99',
left(table1.column2, 2),
max(table1.column3),
'Yes',
'Test'
FROM table1
WHERE NOT EXISTS ( SELECT *
FROM TABLE2
WHERE table2.column1 = table1.column1
and left(table2.column2, 2) = left(table1.column2, 2)
)
AND table1.column4 = 'Yes'
GROUP BY table1.column1, Left(table1.column2, 2)
I removed trims as most likely they are not required.
RTRIM is not needed for sure, and LTRIM looks like just a sign of a bad habit.
Please check your data to make sure my assumption is correct.
_____________
Code for TallyGenerator