This weekend I tried to write a new query that seemed pretty simple…
To abstract the problem a bit… I have two tables… where some column values are code references (matrixes).
My task is to identify a section of data from a code group that satisfies a few levels of hard criteria and report back the group of data that matches a string code criteria.

I wrote:

SELECT //all associated clmns
table 1.primarykey //rec_num
, table1.code1 //criteria1
, table1.code2 //criteria2
, table1.code3 //criteria3
, table1.txt //criteria4
, table2.stringcode //6 positions
, table2.primary key //rec_num
FROM table2 //where my data lives
INNER JOIN table1
ON table2.primarykey=table1.primarykey //tie together with alias
WHERE
( (table1.codelevel1=”codevalue_XX”) //code criteria1
AND
(table1.codelevel2=”codevalue_XXXXX”) //code criteria2
AND
(table1.codelevel3=”codevalueXXXX”) //code criteia3
AND
(table1.txtcode=”XX”) //code criteria4
AND
(table2.stringcode=like”%%13%%”) //report all with “13” in the middle two positions

);

I keep getting empty sets and I’m a little confused. I’m pretty new to this and may be trying to push a cow uphill with a rope…so I thought I’d better check in.

I apolgize in advance for poor syntax.

Thanks in Advance as well...I don't know how to proceed.

Thanks,
Hittman
Andy

you probably got a syntax error but did not know it, as i am guessing you ran this from php without error reporting turned on (the giveaway was your use of two consecutive percent signs instead of one)

the syntax error is here: table2.stringcode=like”%%13%%”

try it like this and try it outside of php first: table2.stringcode LIKE '%13%'

I will try that when I get home later tonite and report back.

Andy,
For ensuring 13 is in middle 2 positions you should probably use a SUBSTR rather than LIKE. Also, would all of those different codes reside on a single row in your code table? I would have thought from your description that you were after multiple codes from that table, which would have meant OR conditions or multiple iterations of the code table.
Dave

It took me a few days to actually understand what you were saying...newbie condition.
I was trying for multiple codes from the table...this is a great help...a little mystery solved.

r937 - Actually, I'm working straight out of the client on the command line but borrowing script code (is that the right vernacular?)from wherever I can. I never was able to get the wildcards to work in any form...I'm gonna try the SUBSTR method next. I also just noticed a little twist in your code example (no =)...I wonder sometimes if I'm wired for this. I guess as long as it is still fun.

Today I'm loading and testing Workbench...should make my life easier if you can believe what you read.

Thanks very much.

Andy

that's because there is no equal sign involved in the LIKE operator

and i only just noticed what you were trying to do with "middle two positions"

in order to check if 13 is in the middle two positions of 6 positions, you can use this --
you were using the "zero or many characters" percent wildcard, whereas you should have been using the "exactly one character" underscore wildcard

it's all explained rather nicely in da manual

no doubt...can't wait to try this...weekend project, you know.

I'll report back...I'm involved in dissecting another thread right now = incoming dumb questions...keep your head down.

Thanks again,
Andy

I just noticed you have a book...so I thought I'd order one...alternate manual, of sorts. Got the page up and wondered if you are involved in shipping? Real question...is it too big a pain to get you to sign it? A little off-topic but it would be cool...no huge deal but waiting to order.

no, sorry, the book is shipped from the publisher, not from me

if you would like a signed copy, i'd have to order one, have it shipped to me, sign it, and then ship it to you

so it would be a tad more expensive for you

alternatively, if you're ever in toronto, i'd be happy to meet you and sign it then

Toronto is not in my foreseeable stars.

Thanks anyway...

you can breathe now.


Andy