SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Checking a number from a range, contained within a range under SQL

    Working on an application where transactions are created that log a range of numbers, i.e.

    SysTranId, RangeFrom,RangeTo


    So user enters a range in the system and I want to check whether any numbers in the users range, are contained within any of the ranges stored in the transactions.

    i.e. Users Range 100010, 100022 (numbers 100010 through to 100022)

    Have any of these numbers been used, regardless of the range (so 100015 could have been used in range 100000 to 1000015 and 100022 could have been used in range 100020 to 100025)

    Is this something that can be done easily in SQL. I can do this by cycling through the table, which while there are a few hundred records won't be too bad, but this application will have hundreds of thousands of records.

    Any ideas gratefully received

    Colin

  2. #2
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Most database systems (since you didn't mention which one I can't be more specific) have a BETWEEN statement, normally used like this:

    SELECT *
    FROM table
    WHERE col BETWEEN 5 AND 100

    Substitute '5' and '100' for the user-inputted values and you're laughing.

    Cheers,
    D.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    disgracian, the problem here is that there isn't a single "col" that the BETWEEN can operate on, there two of them -- RangeFrom and RangeTo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    let's set up a timeline to illustrate the problem

    let's say that the user enters numbers in a START and END range, and you have transactions defined using RangeFrom and RangeTo numbers

    here are all the ways that a transaction overlap the user's numbers --
    Code:
                         START                  END            
                           |                     |               
    1 RangeFrom---RangeTo  |                     |               
                           |                     |               
    2           RangeFrom--|--RangeTo            |               
                           |                     |               
    3                      | RangeFrom---RangeTo |               
                           |                     |               
    4          RangeFrom---|---------------------|---RangeTo       
                           |                     |               
    5                      |          RangeFrom--|--RangeTo 
                           |                     |               
    6                      |                     |  RangeFrom---RangeTo
    with me so far?

    now, all we have to do is choose every transaction that satisfies one of the conditions 2 through 5

    the easiest way is like this --
    Code:
    ... WHERE RangeTo >= START  /* eliminates case 1 */
          AND RangeFrom <= END  /* eliminates case 6 */
    notice that it's an AND which means both of those have to be true
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ottawa, Canada
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy. I could have sworn I tried that a while back, but it didn't work. Oh well, all works now. Thanks again. Colin


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
  •