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.
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
( (table1.codelevel1=”codevalue_XX”) //code criteria1
(table1.codelevel2=”codevalue_XXXXX”) //code criteria2
(table1.codelevel3=”codevalueXXXX”) //code criteia3
(table1.txtcode=”XX”) //code criteria4
(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.
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%'
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.
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.
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.
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.