CODEWITHSUNDEEP
mysqlsql
Duc Hoang
Duc Hoang

Reputation: 345

How to query a date between two other dates in another table

I need to query a date with a value between two other dates that come from another table. Something like this:

select * from table1 where date_table1 BETWEEN (select date1,date2 from table2 where id=1)

How can I do that in SQl?

Upvotes: 3

Views: 3069

Answers (3)

AkshayS
AkshayS

Reputation: 34

This should work provided each sub-query returns exactly one value



    SELECT * from table1 t1 
    WHERE t1.date_table1 
    BETWEEN 
    (SELECT t2.date1 from table2 t2 WHERE t1.id=1) 
    AND (SELECT tt2.date2 from table2 tt2 WHERE t1.id=1);

Upvotes: 0

Andrey
Andrey

Reputation: 478

I am not sure what you are trying to do but the way you phrased it the question seems incomplete. Based on what you have provided, I think something like this will work:

select t1.*
  from table1 t1
 inner join table2 t2 on t2.date1 <= t1.date_table1 and t1.date_table1 < t2.date2
 where t2.somefield = [somelimitingcondition]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Just use join or exists. As you have written it:

select t1.*
from table1 t1
where exists (select 1
              from table2 t2
              where t1.date between t2.date1 and t2.date2
             );

Upvotes: 5

Related Questions