SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Extract rows with different string

    Extract rows with different string

    Hello people, this is my first post... I hope your help

    The Db is MySQL 5.0.45-community-nt via TCP/IP

    TableA:
    Code:
    ID	CODE
    1	XX00138410
    TableB:
    Code:
    ID	CODE
    18	XX00-1-380410
    If I execute this query:
    Code:
    SELECT
    A.CODE, B.CODE
    FROM TableA A
    INNER JOIN TableB B ON
    A.CODE = REPLACE(B.CODE, "-","")
    The result is 0 rows fetched, because the code of TableB is XX001380410:
    in the code of TableB I have other zero: XX001380410

    Can someone help me?
    Thanks in advance.
    Chevy

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What is the rule by which you consider these rows a match?

    You need to be able to clearly, unambiguously write that down before you can write a query which expresses it.

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok Sir, I will be more explicit:

    TableA:
    Code:
    ID	CODE
    1	XX00138410
    TableB:
    Code:
    ID	CODE		NAME
    18	XX00-1-380410	BECKAMS
    I need this output:
    Code:
    A.ID	A.CODE		B.NAME
    1	XX00138410	BECKAMS
    Thanks in advance.
    Chevy

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That does not answer the question.

    Why do you equate XX00138410 to XX00-1-380410?

    Why is it OK to ignore that 0? What if it was a different number? What if the two codes don't match in a different position?

    It is simple to write a query that matches these two rows, but without knowing the rule in your head, that query will not work for any other two rows with different values.

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    [QUOTE=Dan Grossman;4539648]That does not answer the question.
    Sorry Sir I thinking explicit my problem.

    Why do you equate XX00138410 to XX00-1-380410?
    I equate XX00138410 to XX00-1-380410 because I need the value of field NAME, recorded in tableB and not in tableA. I working only values of fields the tableA, but this field NAME not existing in tableA and not know how to recover...

    Why is it OK to ignore that 0? What if it was a different number? What if the two codes don't match in a different position?
    The 0 value (and "-" symbol) in the string is always in the same location

    Thanks.
    Chevy

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    The 0 value (and "-" symbol) in the string is always in the same location
    okay, then you're in business
    Code:
    SELECT a.id
         , a.code
         , b.name
      FROM TableA AS a
    INNER 
      JOIN TableB AS b
        ON b.code = CONCAT(SUBSTRING(a.code FROM 1 FOR 4)
                          ,'-'
                          ,SUBSTRING(a.code FROM 5 FOR 1)
                          ,'-'
                          ,SUBSTRING(a.code FROM 6 FOR 2)
                          ,'0'
                          ,SUBSTRING(a.code FROM 8 FOR 3)
                          )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by r937 View Post
    okay, then you're in business
    Code:
    SELECT a.id
         , a.code
         , b.name
      FROM TableA AS a
    INNER 
      JOIN TableB AS b
        ON b.code = CONCAT(SUBSTRING(a.code FROM 1 FOR 4)
                          ,'-'
                          ,SUBSTRING(a.code FROM 5 FOR 1)
                          ,'-'
                          ,SUBSTRING(a.code FROM 6 FOR 2)
                          ,'0'
                          ,SUBSTRING(a.code FROM 8 FOR 3)
                          )

    Thanks for your suggestion.


Bookmarks

Posting Permissions

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