Wild card join

Question

Post reply

Wild card join

Steve

SSCommitted

Points: 1844
More actions
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

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

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

sainswor99 SSC Veteran Points: 230 More actions · Answer 1

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

Steve SSCommitted Points: 1844 More actions · Answer 2

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

Jonathan

SSC-Insane

Points: 20427

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

Steve SSCommitted Points: 1844 More actions · Answer 4

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

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

Steve SSCommitted Points: 1844 More actions · Answer 6

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