Post reply

Wild card join

  • November 21, 2003 at 4:00 pm

    #82862

    Hello Gurus

    I have a question about having a wild card type join between tables

    Here is the data

    Table Name:Code

    code des

    ---------- --------------------

    % wild card

    10 some code

    11 some code

    12 another code

    13 yet another code

    Table Name:Match

    code

    ----------

    10

    11

    13

    17

    I started with the following sql statement

    select * from match left join code on match.code like code.code

    which yielded the following results

    code code des

    ---------- ---------- --------------------

    10 % wild card

    10 10 some code

    11 % wild card

    11 11 some code

    13 % wild card

    13 13 yet another code

    17 % wild card

    I am looking for results like this

    code code des

    ---------- ---------- --------------------

    10 10 some code

    11 11 some code

    13 13 yet another code

    17 % wild card

    Any help would be greatly appreciated.

    Steve

  • November 21, 2003 at 8:45 pm

    #483254

    Instead of joining on a wild card, can you use either an embedded description or a key for the wildcard phrase?

    An embedded description would look like the following (assuming that you get rid of the wildcard row):

    SELECT match.code, code.code,

    DES = IsNull(code.des, 'wild card description here')

    FROM match LEFT JOIN code ON match.code = code.code

    If you think that your wild card description will change often, then you may want to create a key in the code table that contains that description (much like your wildcard now) that you will never use:

    SELECT match.code, code.code,

    DES = IsNull(code.des, (SELECT des FROM code WHERE code = '%'))

    FROM match LEFT JOIN code ON match.code = code.code

    I would recommend that you not use a wildcard as a fixed value; it's just poor technique, and may give you fits later on. Instead consider using an unrealistic index (like 0 or 9999999, etc)

    HTH,

    Stu

  • November 24, 2003 at 6:14 am

    #483323

    Thanks for the feedback.

    I wish it were as simple as that. The example I gave is an over simplified version of a more complex problem. The des field is only one of several columns that I would need. In the application, the “Code” table has several columns that I would have to associate to the code column. IE

    Code Table

    Code, Des, Type, Section, Name, …

    I had already different permutations of the IsNull function, but this did not achieve the results for which I was searching. As for changing wild card, as with most DBAs, I am stuck with legacy code. It would take more passes than I have the time or resources to deal with at the moment.

    Steve

  • November 24, 2003 at 6:55 am

    #483331

    I agree with Stu; this design is poor. As you're apparently stuck with it, try something like this:

    

    SELECT m.code, ISNULL(c.Code,n.Code), ISNULL(c.Des,n.Des)

    FROM Match m LEFT JOIN Code c ON m.Code = c.Code

    JOIN Code n ON n.Code = '%'

    --Jonathan



    --Jonathan

  • November 24, 2003 at 7:16 am

    #483336

    Thanks Jonathan

    I do so appreciate having a forum that seems to have the answers for every problem that I come up with.

    As far as design goes, my intention is to correct the "worst practices" of our legacy code. This is just one of many. How would you, or any one that would like to reply, handle the following business logic.

    The customer has a given set of codes that are described by multiple columns.

    ie

    code:1

    type:P

    Desc:checking debit

    WavDesc:some wave file

    ... (other columns)

    Requirement

    The default value and the corresponding columns of the default must be returned if the code does not exist in the table.

    The default value must be configurable.

    ie the description, WavDesc and Type is different from customer to customer.

    Should I, as Stu suggested, create a “known” default value (99999999 instead of %) that is returned for all non-matched codes. Other than having a number as a wild card, I do not see how this changes the design.

    Steve

  • November 24, 2003 at 7:31 am

    #483341

    How about using NULL as your "wildcard"? That would allow the correct data types for the codes, as you wouldn't need to accomodate a char among numbers as in your example.

    

    SELECT m.code, ISNULL(c.Code,n.Code), ISNULL(c.Des,n.Des)

    FROM Match m LEFT JOIN Code c ON m.Code = c.Code

    JOIN Code n ON n.Code IS NULL

    --Jonathan



    --Jonathan

  • November 24, 2003 at 7:39 am

    #483346

    That is probably a good idea, but I still have to use chars as my datatype, because the codes are alpha numeric. Most of the time they are just numbers, but there is always at least one or two partially or totally alpha codes.

    Thanks for the suggestion. I will research implementing Nulls instead of the % that I am currently using.

    Steve

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply