Too many results when only expecting 1

[Tommy.b]

Hey

using the following sql

SELECT DB_SALGSPRIS from pris_interval WHERE DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END >= '11.78'
SELECT DB_CUSTOMER  from pris_interval WHERE AND DB_INTERVAL_START < '7.19'AND DB_INTERVAL_END >= '7.19'

I get multiple results, 3 from first, and 2 from the second to be precise, what I am wondering about is why, because when I look at the table, using the given values. I only get 1 result, from each statement.

The results the statements give are

db_salgspris:
59.00
149.00
1099.00

db_customer
149.00
1099.00

A snip from the table is as follows:

DB_Salgspris db_interval_start db_interval_end db_customer
59          |         2       |   3           | 59
99          |         7       |   8           | 149
149         |         11      |   12          | 199
699         |         65      |   68          | 1099
1099        |         102     |  110          | 1699

What I dont get it why there are 3 results for one query, and 2 for the other, when I look at the table, I would expect only to get one, namely 99 for the first query, and 199 for the second query.
Any able to clarify this?

[tesuji]

Instead of:

... DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END >= '11.78'

should it not be:

... DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END <= '12.78'
? Sorry, wrong too

or better:

...  '10.78' < DB_INTERVAL_START AND DB_INTERVAL_END <= '12.78'  -- to get 5th line
this works

-- tesu

[Tommy.b]

Hey Tesu,

one might think that, but in actuallity, the idea is to find in the first case, the value db_salgspris, when value is between interval_start and interval_end, in which case it is correctly

DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END >= '11.78'

because the value must be where interval_start is less than the value, and interval_end is larger than or equal to the value, in this case

DB_Salgspris db_interval_start db_interval_end db_customer
149         |         11      |   12          | 199

so 11.78 is larger than 11(db_interval_start), and less than or equal to 12(db_interval_end)
in which case it should give 149, and it is one of the results but it should only give that result and not

59.00
149.00
1099.00

[pritaeas]

Are your columns perhaps varchar instead of numeric ?

[tesuji]

I am back again. You are completely right, I have mistaken your problem. However, the most important thing is that:

Given your table:

DB_Salgspris db_interval_start db_interval_end db_customer
59          |         2       |   3           | 59
99          |         7       |   8           | 149
149         |         11      |   12          | 199
699         |         65      |   68          | 1099
1099        |         102     |  110          | 1699
 
If I do this:

SELECT DB_SALGSPRIS from pris_interval WHERE DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END >= '11.78'

I get:

DB_SALGSPRIS
------------
149

If I do this:

SELECT DB_CUSTOMER  from pris_interval WHERE DB_INTERVAL_START < '7.19'AND DB_INTERVAL_END >= '7.19'

I get:

DB_CUSTOMER
-----------
149

These results are correct. There aren't any further rows but the listed two. So something must be wrong with your database. I did both selects on Sybase database (MS SQL Server's mother).

-- tesu

[Tommy.b]

pritaeas, yes you are correct - I missed this when looking at the table, but they are indeed varchars. I've casted the coloums to floats and it now correctly results in just one result.

Tesu, thanks for your input :)