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. Thanks, Brian.
