Querying information between two values

[onelung]

Alright, I am going to to my best to explain this.

I have an item that has a range of consecutive numbers associated with it. So Item1 has the vaules of #start = 1111 and #end = 1113. I then have a list of about 2 thousand numbers that I need to associate to items, and the only way to do this is going to be to find them between the #start and #end of an item. So for example if I have this:

Item2 has #start = 1114 and #end = 1116 I would like this item to show as a result when I search for #1115.

I have no idea how to go about this since I have so many numbers to search for. It would be simple if it was only one number to find, I was just use the between function, but with this many I am facing quite the task. Also, when I pull the information, I need the results to be two columns. One column have the item, and the other column have the number that I searched for so I can tie the two together. I am not sure if this is even possible.... Anyone have any suggestions?

[michaelb]

You was not clear on how your data is organized.
I can imagine table with two columns, where first one is the item, and second is a long string containing integer values separated by space.
Another option is to have these integer values in a different table associated with item by foreign key.

In the first case one way of getting what you need (not necessarily the best way) is to use the like operator:

select item, 115 from myTable where (' ' ||  item_numbers  || ' ') like '% 115 %';

115 here is an arbitrary number, probably would be replaced by a variable in your code.

Note the trick with pasting spaces around the column and around the value.
It is used to capture the first and the last numbers in the query (if this is the requirement)
These numbers have space on one side only, and otherwise will be missed.

[onelung]

Itemname      NumberStart       NumberEnd
Item1         1115              1118
Item2         1120              1125
Item3         1126              1130
Item4         1135              1140
Item5         1142              1150

So this is essentially how the data is organized. Granted there is other information in the table that I am querying, but this is all that is relevant.

I have a list of numbers, and I need to find which item they are associated to. If I were to do this one by one, in the where section of the query I would use

where '1117' is between NumberStart and NumberEnd

This would return Item1 as the result. So I guess my question has two parts. First part:
Is it possible to do something like this with a list of about 2000 numbers? Also, the amount of ItemNames involved is in the 800,000 range. I am looking for a way to do this in one query instead of having to go through the list of 2000 numbers 1 by 1.

Second part:
In the results I would like to columns. First column being the ItemName, and the second column being the number that I searched for tied to the ItemName in the first column.

I hope this is a more accurate way of explaining things.

[onelung]

Apparently my edit window has expired, but that needs to read "two columns" in the results. My apologies.

[michaelb]

I'll try to answer, although I'm not an Oracle expert, and I hope we have some in the community, so they'll correct me where I'm wrong...

>> First part:
>> Is it possible to do something like this with a list of about 2000 numbers?
If these numbers are dynamically generated and passed with the query you'll have to consider the limit on the query string. In the older days some APIs (such as ODBC and others) had a very small query max-size, about 1024 bytes.
Oracle itself, unless I'm mistaken puts this limit at 16MB

>> Second part:
>> In the results I would like to columns. First column being the ItemName,
>> and the second column being the number that I searched for tied to the
>> ItemName in the first column.

It is possible, at least with design where numbers are stored in a separate table - INUMBERS

select N.inumber, I.itemname from inumbers N, items I where N.inumber between I.numberstart and I.numberend;
or
select N.inumber, I.itemname from inumbers N join items I on (N.inumber between I.numberstar and I.numberend);

And you can also display all numbers used for query, those that don't have a match will have an empty second column:

select N.inumber, I.itemname from inumbers N left outer join items I on (N.inumber between I.numberstart and I.numberend);
 

I tested this with a very small tables, but considering that your table ITEMS has 800000 records you will have to take a close look at performance.
I won't be surprised if on Windows it'll choke the system...

[michaelb]

Just realized that you're running on SQL server, not Oracle.
I'm very sorry about the confusion, chances are it all still applies, but you have to test the code yourself - I don't have SQL server available.

[onelung]

I am installing MSSQL on my local PC and I am going to load all of the numbers into a table within there so I can have that available. After this is done I will test this code and let you know how it goes.... crossing my fingers.


-onelung