I have a table like this:
CREATE TABLE np (
fromId VARCHAR(6),
toId VARCHAR(6),
serviceId INTEGER,
PRIMARY KEY (fromId, toId)
);
and I insert these values:
INSERT INTO np VALUES ( 0, 5, 2);
INSERT INTO np VALUES ( 40, 43, 3);
INSERT INTO np VALUES (440,499, 3);
INSERT INTO np VALUES (500,599, 1);
I want to list only the rows who have, for a particular value, for
example *42*
a min bound "fromId" and max bound "toId".
for example:
if I run this query in *sqlite version 3.3.6* I obtain (two rows):
sqlite> SELECT * from np WHERE (42>=fromId) AND (42<=toId);
fromId toId serviceId
--------------- --------------- ---------------
0 5 2
40 43 3
and if I run the same query in Mysql I obtain (one row):
mysql> SELECT * from np WHERE (42>=fromId) AND (42<=toId);
+--------+------+-----------+
| fromId | toId | serviceId |
+--------+------+-----------+
| 40 | 43 | 3 |
+--------+------+-----------+
1 row in set (0.03 sec)
this is correct because *40 <= 42 <= 43*
I have the same result using *BETWEEN* operator.
can you help me to to explain to me this "strange" (for me)
behavior?
bye Andrea.