CODEWITHSUNDEEP
sqljoinadvantage-database-server
egg
egg

Reputation: 373

SQL LEFT JOIN with partial string match

I want to make a left join, based on a partial string match.

The field I am looking up (REC_LIST) contains (normally) just 1 invoice number. But sometimes, there are multiple numbers listed in the field.

Therefore, I try to do something like this, just find my match, regardless of whatever else is in the field:

SELECT DAB700.DATUM,DAB700.BELEG_ART, DAB700.BELEG_NR, DAB050.ANUMMER
FROM "DAB700.ADT" DAB700
left join "DAB050.ADT" DAB050 on DAB700.BELEG_NR LIKE '%'+DAB050.REC_LIST+'%'
WHERE DAB700.BELEG_NR = '337847'

In this example, I'm forcing it to find one specific item, just for error checking. Problem is, it gives me the left table - but doesn't find any match (though I know it exists).

Is my code correct? Is there another way to achieve this?

Sample of REC_LIST:

rec_list

output

Upvotes: 0

Views: 2251

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think your comparison is backwards:

SELECT DAB700.DATUM,DAB700.BELEG_ART, DAB700.BELEG_NR, DAB050.ANUMMER
FROM "DAB700.ADT" DAB700 LEFT JOIN
     "DAB050.ADT" DAB050 
     ON DAB050.REC_LIST LIKE '%' + DAB700.BELEG_NR + '%'
WHERE DAB700.BELEG_NR = '337847';

The "bigger thing" (i.e. the "list") goes before the LIKE. The "smaller thing" (i.e. the particular value) is part of the pattern.

Upvotes: 1

persian-theme
persian-theme

Reputation: 6638

try this query

SELECT DAB700.DATUM,DAB700.BELEG_ART, DAB700.BELEG_NR, DAB050.ANUMMER
FROM "DAB700.ADT" DAB700, "DAB050.ADT" DAB050
WHERE DAB700.BELEG_NR = '337847' AND DAB700.BELEG_NR LIKE '%' + DAB050.REC_LIST + '%'

Upvotes: 1

Related Questions