mysql query for between operator with range [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

i have sample records ranking table

   SN开发者_开发问答O      NAME            TYPE    RANK1-RANK2      DATE    
2   sd sdf ds   Weekly  4    - 8    20-Sep-2011 19:23:02          
3   asd asd     Weekly  1    - 6    21-Sep-2011 19:23:02          
4   sdsdsfsd    Weekly  1    - 1000 22-Sep-2011 19:23:02

in the above example one records i have 20 sept 2011 i have ranks 1 - 8 , from form i used to select rank1 and rank2 , that rank1 and rank2 should not be in this range if insert to be success, other wise it should not be insert. for that i need select sql query can u any one answer this select sql query..

test case for SNO 2 record

chosen from form

                     rank1 - rank2 insert 
                       1    -  3     true
                       2    -  6     false
                       5    -  7     false
                       10   -  100   true

---

You can use this query to see if there is a record that prevents you from inserting. If it returns 1, then the insert would be prohibited.

SELECT COUNT (*) FROM table
WHERE rank1 NOT BETWEEN MIN(incomingRank1, incomingRank2) - 1 
                    AND MAX(incomingRank1, incomingRank2) + 1
  AND rank2 NOT BETWEEN MIN(incomingRank1, incomingRank2) - 1 
                    AND MAX(incomingRank1, incomingRank2) + 1
  AND date = incomingDate

---

if i understood correctly; when you get a value other than 0 from this query, you can insert the data.

select count(*) from table t 
where 
(t.rank1 not between 4 and 8)
and 
(t.rank2 not between 4 and 8)
and
t.date = '2011-09-20'