SQL Server - OUTER JOIN with CHARINDEX()

Hello,

Given are Tables [T1] , [T2] and [B].

[T1] and [T2] are inner joined join trough Field x.
[T1] and [T2] both have a field "Substring".
[B] has  a field "String".

This is a short draft of the tables and their joins:

[B] <String   Substring>  [T1] < x   x > [T2] <Substring String > [B]


I can test a match of Substring to String by "WHERE CHARINDEX(....) > 0".


Now I want the following to code:

Show me all lines where T1.x = T2.x
and:
where T1.Substring has a match in B.String and T2.Substring has *not*
OR (vice versa):
where T2.Substring has a match in B.String and T1.Substring has *not*

This is like an OUTER JOIN, but I think have to use this CHARINDEX() - stuff.


How can I code the SQL - Condition above?


Thank You

Joachim


[SQL 2K]

Two queries that should get you the results if I understood correctly what
is needed:

SELECT T1.col_substring,
T2.col_substring
FROM T1
JOIN T2
ON T1.x = T2.x
WHERE EXISTS
(SELECT *
FROM B
WHERE B.col_string LIKE '%' + T1.col_substring + '%'
AND B.col_string NOT LIKE '%' + T2.col_substring + '%')
OR EXISTS
(SELECT *
FROM B
WHERE B.col_string NOT LIKE '%' + T1.col_substring + '%'
AND B.col_string LIKE '%' + T2.col_substring + '%')

SELECT T1.col_substring,
T2.col_substring,
B1.col_string,
B2.col_string
FROM T1
JOIN T2
ON T1.x = T2.x
LEFT OUTER JOIN B AS B1
ON B1.col_string LIKE '%' + T1.col_substring + '%'
AND B1.col_string NOT LIKE '%' + T2.col_substring + '%'
LEFT OUTER JOIN B AS B2
ON B2.col_string NOT LIKE '%' + T1.col_substring + '%'
AND B2.col_string LIKE '%' + T2.col_substring + '%'
WHERE B1.col_string IS NOT NULL
OR B2.col_string IS NOT NULL

HTH,

Plamen Ratchev
http://www.SQLStudio.com