SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    556
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    SQL not returning result properly

    Hi,

    I have the following DB with data:

    Code:
    CREATE TABLE IF NOT EXISTS `products` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `RngPrefix` varchar(10) NOT NULL,
      `StrRange` varchar(10) NOT NULL,
      `EndRange` varchar(10) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
    
    INSERT INTO `products` (`id`, `RngPrefix`, `StrRange`, `EndRange`) VALUES
    (1, '', 'AA001', 'AA999'),
    (2, '', '2h0001', '2h3333'),
    (3, '', '95001', '97000'),
    (5, '', '2005N', '9999N'),
    (6, '', '2003DN', '5003DN'),
    (7, '', '3002LM', '9002LM'),
    (8, '', '0A', '9Z'),
    (9, '', '458A', '5124A'),
    (10, '', 'CC001', 'CC2000'),
    (11, '', '29220002', '292201000'),
    (12, '', 'N001', 'N200');
    And I am running the following query:

    Code:
    SELECT * FROM products WHERE 'CC456' >= StrRange AND 'CC456' <= EndRange
    Why its not returning record # 10 ? And if you search for CC111 or CC1111 then it returns record 10. Why ?
    The above database has ranges in it and i need to search between those ranges. What will be the best way ?

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Tapan View Post
    The above database has ranges in it
    unfortunately you seem to expect them to behave as compound ranges consisting of an alphabetic portion and a numeric portion

    CC456 does not fall between CC001 and CC2000, it actually comes after CC2000

    that's because the comparison is made on a character by character basis, from left to right
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    556
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Can you please suggest me how to design the db for this so the search is done perfectly. Please guide.

    Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Tapan View Post
    Can you please suggest me how to design the db for this so the search is done perfectly.
    well, you could start by splitting your alphabetic and numeric parts into separate columns

    but i'm not sure that's going to be enough
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    556
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I am already seperating the starting alphabets and ending ones into 2 fields called: Prefix and Postfix..
    Then I in php side, fetched all the prefix and postfix and checked if the specified number has matching prefix / postfix.
    If found then i was able to narrow down my search to that specific prefix/postfix. I guess this should work.

  6. #6
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    556
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I somehow managed to do it. Now only mixed ranges / series which cannot be seperated like: 0A to 9Z are the issue. How to deal with them ?

    Thanks.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Tapan View Post
    Now only mixed ranges / series which cannot be seperated like: 0A to 9Z are the issue. How to deal with them ?
    i think you'll find they will work as is
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    556
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Ya done that too. Its showing some wrong records also but they are minimal and can be ignored easily.

    Thanks.


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
  •