Subject SQL help, please
Author brian
Hi folks,

I'm trying to help a friend out with writing a query, and it's giving
me headaches. Can someone point me in the right direction, please?

Simplifying the problem, this is a two table database. The first table
contains two fields, one is a character description, the other an
integer code. The character description is what the end user keys in
to do the search, and it has to work on partial strings.

The main database contains a character field which can contain an
arbitrary number of these codes concatenated together as a
comma-separated list - let's say 10 codes maximum, although I can't
see that matters, and yes, it's possible for this field to be empty.
These codes are not padded, so the character field could contain
1,11,21,101 for example, and 1 must only match with 1, not with the
other three.

What the query needs to do is a SELECT * on the main database where
the field of concatenated codes contains any one of the zero or more
codes returned by a subquery on the lookup table.

Can someone point me in the right direction, please? This is testing
my (very rusty) SQL beyond breaking point. Yes, I know some functions
can vary across different SQL implementations (the differences between
Oracle and DB2 used to be a major curse when I was doing this sort of
thing for a living ~20 years ago) but assume Firebird, I think I'm
capable of sorting out any differences there may be between Firebird
and the phone-based database that's actually being used.