Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    23

    Unanswered: 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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    23

    Thank you Sir,

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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  5. #5
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2010
    Posts
    23

    that'll work

    Toronto is not in my foreseeable stars.

    Thanks anyway...

    you can breathe now.


    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •