n00b SQL Question

Results 1 to 2 of 2

Thread: n00b SQL Question

  1. #1
    Join Date
    Dec 1969

    Default n00b SQL Question

    I need help with the syntax for a SQL statement. I need to get the data from a table based on the ranges of two fields.<BR><BR>They are both numeric fields. The real problem arises because the values are cyclical. The first field iterates from 1 to 10 and the second field interates from 1 to 25. So you get a list of values like:<BR><BR>1-18<BR>1-19<BR>1-20<BR>2-1<BR>2-2<BR><BR>So I may need the values from 1-10 to 2-10. Using the SQL funciton BETWEEN will only get me a fraction of the values I need, even compensating for the fact that BETWEEN in not inclusive. That is, if I need the values from 2 to 6 I must write it as &#039;BETWEEN 1 AND 6&#039;.<BR><BR>The cyclical nature of the values is too much for my knowledge of SQL, so any help would be most appreciated.<BR><BR>NOTE: I am not allowed to change the table&#039;s structure in any way.<BR><BR>Thanks for any help.<BR><BR>Lastly, I always pay back for any help I get, but on the internet this usually means providing a link of some sort.<BR><BR>If you don&#039;t know what/who Trogdor the Burninator is, go here:<BR><BR>http://www.homestarrunner.com/sbemail58.html<BR><BR>If you do know what/who that is, go here: <BR><BR>http://www.homestarrunner.com/trogdor.html

  2. #2
    Join Date
    Dec 1969

    Default Let me make sure...

    ...I have this straight. Limiting your DB values to a usable range for this forum, you have something like this in a table:<BR><BR>field1 -- field2 <BR> 1 -- 1<BR> 1 -- 2<BR> 1 -- 3<BR> 1 -- 4<BR> 1 -- 5<BR> 2 -- 1<BR> 2 -- 2<BR> 2 -- 3<BR> 2 -- 4<BR> 2 -- 5<BR> 3 -- 1<BR>etc.<BR><BR>And now somebody asks for all records from (example) 2-3 to 4-2.<BR><BR>And you want to know how to handle it?<BR><BR>To me, the obvious solution is to multiply the field1 value by (say) 100 and add the field 2 value. Both for the test values and for the db values.<BR><BR>SO:<BR><BR>&#060;%<BR>f1start = 2 &#039; presumably, these come from form input<BR>f2start = 3<BR>f1end = 4<BR>f2end = 2<BR>&#039; start calculations<BR>fstart = f1start * 100 + f2start<BR>fend = f1end * 100 + f2end<BR><BR>SQL = "SELECT * FROM table WHERE field1*100+field2 BETWEEN " & fstart & " AND " & fend<BR>...<BR>%&#062;<BR><BR>Why make it any harder than that?<BR><BR>

Posting Permissions

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