If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > inner join, empty set

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-29-10, 15:26
hittman hittman is offline
Registered User
 
Join Date: Mar 2010
Posts: 23
inner join, empty set

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
Reply With Quote
  #2 (permalink)  
Old 03-29-10, 15:33
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
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%'
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 03-29-10, 17:43
hittman hittman is offline
Registered User
 
Join Date: Mar 2010
Posts: 23
Thank you Sir,

I will try that when I get home later tonite and report back.
Reply With Quote
  #4 (permalink)  
Old 03-30-10, 09:06
dav1mo dav1mo is offline
Registered User
 
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
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
Reply With Quote
  #5 (permalink)  
Old 04-10-10, 11:12
hittman hittman is offline
Registered User
 
Join Date: Mar 2010
Posts: 23
Quite right 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
Reply With Quote
  #6 (permalink)  
Old 04-10-10, 11:57
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
Quote:
Originally Posted by hittman View Post
I also just noticed a little twist in your code example (no =)...
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 --
Code:
table2.stringcode LIKE '__13__'
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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old 04-10-10, 12:46
hittman hittman is offline
Registered User
 
Join Date: Mar 2010
Posts: 23
da manual - r937

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.
Reply With Quote
  #8 (permalink)  
Old 04-10-10, 13:03
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #9 (permalink)  
Old 04-10-10, 13:49
hittman hittman is offline
Registered User
 
Join Date: Mar 2010
Posts: 23
that'll work

Toronto is not in my foreseeable stars.

Thanks anyway...

you can breathe now.


Andy
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On