Left Join string comparison

[MattSmithProg]

Hi all,

I know this query isn't very efficient but I can't find a way to make it work any better. The problem I have is that I need to compare a one column string (Surname, FirstName) on one table with two columns of data (Surname & FirstName). What further complexes it is the way in which some of the names have been recorded in the original table. They sometimes have used a preferred name!!! Oh would I love to rewrite the whole DB. Anyway here is the query.

SELECT
T1.Identifier DocId,
T1.Title,
T1.Owner,
T8.CostCentre Cost_Centre,
FROM
EPUB..PublishingArticle T1,
corporate..vwOfficerV2 T8
WHERE
T1.Identifier = IS NOT NULL AND
(T1.Owner *= LTRIM(RTRIM(T8.Surname)) + ', ' + LTRIM(RTRIM(T8.firstname)) OR
T1.Owner *= LTRIM(RTRIM(T8.Surname)) + ', ' + LTRIM(RTRIM(T8.preferredNm))) AND
T8.CostCentre IS NOT NULL
ORDER BY
1

Any ideas would be extremely welcome.

Matt Smith

No two nulls are the same

 

[royjimenez]

u could join surname + firstname in other field for avoiding to use ltrim and rtrim in your query. U should try to avoid NULL fields, u could change them to NOT NULL and setting a default value.

* U have to change the application, but u will get more efficient with an index.

If it's a frecuent query u could make an index with the changes above.

Regards

The life is too short to cry and long enough to try it... God bless us.