Finding a value within a value using a like join query

Question

Post reply

Finding a value within a value using a like join query

cimbom_kid

SSC-Addicted

Points: 469
More actions
August 17, 2006 at 9:55 am

#115096

Hi All,
I have this query:
SELECT code_all.*
FROM C_CODES code_all
LEFT JOIN (SELECT t.t_code
      FROM (SELECT * FROM Stories WHERE story_dt BETWEEN '2006-08-10 09:00' AND '2006-08-10 10:00') d
         LEFT JOIN C_CODES t ON ' ' + d.topic_code + ' ' LIKE '% ' + t.t_code + ' %'
      GROUP BY t.t_code) code_found
ON code_all.t_code = code_found.t_code
WHERE code_found.t_code IS NULL
C_CODES table contains a code per row: ie:
AU, GB, USA, TR, DE
Stories table contains MULTIPLE codes per story ie:
Story1     AU GB USA TR
Story2     GB AU TR USA
Is there any way by running a query similar to the above it can show me the missing country not used: DE, or do i need to run an SP..
I think i'll start writing the SP
Thanks In Advance,
Sal

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>I think i'll start writing the SP

That's 1 approach.

A better approach in the long run would be to design the database correctly using standard normalization techniques.

If a "story" can have multiple "codes", the solution is not to append the codes into 1 column of the Stories table in the data model. If you do that, you might as well throw away SQL and just use text files as your data store.

You need a StoryCode intersection table.

cimbom_kid SSC-Addicted Points: 469 More actions · Answer 2

Thanks for you response. The design is totally crap, legacy system! SP's the only way to go i think.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

Your query looks like it should work. You could simplfy it by trying something like:

SELECT A.t_Code

FROM C_CODES A

LEFT JOIN Stories S ON S.story_dt BETWEEN '20060810 09:00' AND '20060810 10:00'

AND ' ' + S.topic_code + ' ' LIKE '% ' + A.t_code + ' %'

WHERE S.topic_code IS NULL

Sergiy SSC Guru Points: 110208 More actions · Answer 4

The only way is to create new table(s) with right structure and create trigger on old table to populate new table on each INSERT/UPDATE/DELETE on old table.

You not gonna notice change in INSERT/UPDATE performance, but your SELECT queries will be really fast.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Cimbom,

The other posters and you are correct... pure crap on the part of the designers of the system. Unfortunately, you sometimes have to deal with such things especially if it's a third party solution that you can't change or the company won't fund the changes you know need to be made...

The other posters are right on the ball... you need to change the tables... OR, you can fake it with some derived tables... The code below is a self supporting proof of principle as to what I'm talking about... I didn't include your dates and all because I didn't want to cloudy up the code so the principle would be missed.

The first 3 sections are just test setup code... the last section is where the rubber meets the road...

--===== If the temporary test table exist, drop them

IF OBJECT_ID('TempDB..#C_Codes') IS NOT NULL

DROP TABLE #C_Codes

IF OBJECT_ID('TempDB..#Stories') IS NOT NULL

DROP TABLE #Stories

--===== Create and populate the test C_Codes table

CREATE TABLE #C_Codes (Country VARCHAR(3) PRIMARY KEY)

INSERT INTO #C_Codes (Country)

SELECT 'AU' UNION ALL

SELECT 'GB' UNION ALL

SELECT 'USA' UNION ALL

SELECT 'TR' UNION ALL

SELECT 'DE'

--===== Create and populate the test #Stories table

CREATE TABLE #Stories (StoryNum VARCHAR(10) PRIMARY KEY, Countries VARCHAR(30))

INSERT INTO #Stories (StoryNum,Countries)

SELECT 'Story1','AU GB USA TR' UNION ALL

SELECT 'Story2','GB AU TR USA' UNION ALL

SELECT 'Story3','USA'

--===== Test data is ready, demo the problem solution

SELECT cj.StoryNum,cj.Country

FROM (--==== Derived table "cj" creates all possibilities of country and story

SELECT scj.StoryNum,ccj.Country

FROM #Stories scj,

#C_Codes ccj

) cj

LEFT OUTER JOIN

(--==== Derived table "n" normalizes the country listings by story

SELECT sn.StoryNum,cn.Country

FROM #Stories sn,

#C_Codes cn

WHERE ' '+sn.Countries+' ' LIKE '% '+cn.Country+' %'

) n

ON cj.StoryNum = n.StoryNum

AND cj.Country = n.Country

WHERE n.Country IS NULL

As a side benefit, derived table "n" (you can run it separately after you run the code once) is the beginnings of the code you would need to write to normalize a new table.

Write back if you have any questions...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)