MySQL Help

[Meteor84]

hello, i am not sure if this is the appropriate place to post this so admins feel free to move my thread if necessary.

i am trying to query using BETWEEN statements for comparing of integers. an example would be:

id       start_num           end_num         name
1              1               10            alan
2             11               23            bala
3             24               30            john
4             31               40            david

my sql query:

SELECT name FROM table
WHERE
(5 BETWEEN start_num AND end_num)
OR (20 BETWEEN start_num AND end_num)
OR (6 BETWEEN start_num AND end_num);

logically i would be getting and i would want to get

1. alan
2. bala
3. alan

strangely i keep getting only

1. alan
2. bala

any idea how do i get the desired results? i have no control over 5, 20 or 6. they could be any random number.

 

[davidktw]

hello, i am not sure if this is the appropriate place to post this so admins feel free to move my thread if necessary.

i am trying to query using BETWEEN statements for comparing of integers. an example would be:

id       start_num           end_num         name
1              1               10            alan
2             11               23            bala
3             24               30            john
4             31               40            david

my sql query:

SELECT name FROM table
WHERE
(5 BETWEEN start_num AND end_num)
OR (20 BETWEEN start_num AND end_num)
OR (6 BETWEEN start_num AND end_num);

logically i would be getting and i would want to get

1. alan
2. bala
3. alan

strangely i keep getting only

1. alan
2. bala

any idea how do i get the desired results? i have no control over 5, 20 or 6. they could be any random number.

There is nothing wrong with the query results. It's your understanding about how the query works that is incorrect.

Each set of criteria is matched against each row from the table and see if it satisfy before the name column can be returned as the result.

Among the 4 rows of data, only the first and second rows satisfy the following criteria

... WHERE (5 BETWEEN start_num AND end_num)
          OR (20 BETWEEN start_num AND end_num)
          OR (6 BETWEEN start_num AND end_num)

What you want is the following

SELECT name FROM table WHERE 5 BETWEEN start_num AND end_num
UNION ALL
SELECT name FROM table 20 BETWEEN start_num AND end_num
UNION ALL
SELECT name FROM table 6 BETWEEN start_num AND end_num;

 

[Meteor84]

There is nothing wrong with the query results. It's your understanding about how the query works that is incorrect.

Each set of criteria is matched against each row from the table and see if it satisfy before the name column can be returned as the result.

Among the 4 rows of data, only the first and second rows satisfy the following criteria

... WHERE (5 BETWEEN start_num AND end_num)
          OR (20 BETWEEN start_num AND end_num)
          OR (6 BETWEEN start_num AND end_num)

What you want is the following

SELECT name FROM table WHERE 5 BETWEEN start_num AND end_num
UNION ALL
SELECT name FROM table 20 BETWEEN start_num AND end_num
UNION ALL
SELECT name FROM table 6 BETWEEN start_num AND end_num;

that works perfectly! thank you! i did test each individually and it works. however when i combined them into 1 select statement the logic kinda screwed up. thanks again.

 

[davidktw]

that works perfectly! thank you! i did test each individually and it works. however when i combined them into 1 select statement the logic kinda screwed up. thanks again.

How is it that the logic is wrong ? Perhaps you can post your actual data set and your returned data set using the UNION ALL queries. No way for me to understand where is wrong and how is it considered wrong for you.

 

[Meteor84]

How is it that the logic is wrong ? Perhaps you can post your actual data set and your returned data set using the UNION ALL queries. No way for me to understand where is wrong and how is it considered wrong for you.

my apologies. i was away and couldn't reply sooner.

There is nothing wrong with the query results. It's your understanding about how the query works that is incorrect.

Each set of criteria is matched against each row from the table and see if it satisfy before the name column can be returned as the result.

Among the 4 rows of data, only the first and second rows satisfy the following criteria

Yes, I know only the first and second row satisfy the following criteria. However, for the 3 queries, I would want 3 results, alan, bala alan. But MySQL somehow doesn't return the last alan.

 

[Meteor84]

Basically I would want my results as:

SELECT name FROM table
WHERE
(5 BETWEEN start_num AND end_num)
OR (6 BETWEEN start_num AND end_num);

result:
alan
alan

 

[davidktw]

Basically I would want my results as:

SELECT name FROM table
WHERE
(5 BETWEEN start_num AND end_num)
OR (6 BETWEEN start_num AND end_num);

result:
alan
alan

I think you have mistaken my answer to you. Did you notice the UNION keyword between the SELECT statements? The whole combination of 3 SELECT statements using the UNION ALL is one complete QUERY.

mysql> select * from abc;
+----+-----------+---------+-------+
| id | start_num | end_num | name  |
+----+-----------+---------+-------+
|  1 |         1 |      10 | alan  |
|  2 |        11 |      23 | bala  |
|  3 |        24 |      30 | john  |
|  4 |        31 |      40 | david |
+----+-----------+---------+-------+
4 rows in set (0.00 sec)

mysql> SELECT name FROM abc WHERE 5 BETWEEN start_num AND end_num
    -> [COLOR="Red"]UNION ALL[/COLOR] SELECT name FROM abc WHERE 20 BETWEEN start_num AND end_num
    -> [COLOR="red"]UNION ALL[/COLOR] SELECT name FROM abc WHERE 6 BETWEEN start_num AND end_num;
+------+
| name |
+------+
| alan |
| bala |
| alan |
+------+
3 rows in set (0.03 sec)