0

i have two fields in a table jobpost like this:-

jp_id jp_min jp_max
1      0       1
2      1       2
3      3       4
4      4       8
5      2       3
6      0       1
What i'm trying to find out is if i search for a job with experience 5-9 or 1-5 i should get 4rth record. But if i search for 1-3 exp then i shouldn't get the 4rth record.
I had tried out like this :-
a)SELECT * FROM jobpost WHERE jp_min >=1 AND jp_max <=5; &
b)SELECT * FROM jobpost WHERE (jp_min >=1 AND jp_max <=5); (this one gives result of 
jobpost which requires 1-5 exp only and not that 4rth record 4-8 exp.)
but it's not giving me the desired results. Can someone give me hint or so.
Here, we are search for two values between two columns. I found many answer which search for a single value between two columns because thats easy by using between statement in two fields.
rahul
  • 841
  • 1
  • 8
  • 18
  • 1
    Possible duplicate of [Mysql: Selecting values between two columns](http://stackoverflow.com/questions/12824871/mysql-selecting-values-between-two-columns) – b3tac0d3 Jan 07 '16 at 05:44
  • i tried with possible duplicate answer like this: SELECT * FROM `jobpost` WHERE `jp_min` >=1 and `jp_max` <=5. but i aint getting the 4rth row where experience required is 4-8, so even 1-5 exp falls inbetween 4-8. – rahul Jan 07 '16 at 05:51
  • Have you tried adding grouping? `GROUP BY jp_id` – b3tac0d3 Jan 07 '16 at 05:58
  • this job post are distinct. so whats with group by endol', please can you give me a clarity. – rahul Jan 07 '16 at 06:00
  • Just out of curiosity - what are the column types of jp_min and jp_max? int, smallint, etc... – b3tac0d3 Jan 07 '16 at 06:02

2 Answers2

0

When searching for an experience range of a-b, the following query should do the trick:-

SELECT * FROM jobpost WHERE 
(a>=jp_min AND a <= jp_max) OR (b>=jp_min AND b<=jp_max) ;
Sarath Chandra
  • 1,850
  • 19
  • 40
  • Nope, its not working. if i search for a job with experience 0-5, i'm not getting the jobpost having exp 3-4, Query like this: SELECT * FROM jobpost WHERE (0>=jp_min AND 0 <= jp_max) OR (5>=jp_min AND 5<=jp_max) ; – rahul Jan 07 '16 at 06:37
0

is this what you want?:-

SELECT * FROM jobpost WHERE (jp_min BETWEEN '1' AND '5') OR (jp_max BETWEEN '1' AND '5') OR (jp_min < '1' AND jp_max > '5');

王奕迪
  • 1
  • 1