SELECT with MySQL a specific date from a range

Question

Hi Guys, i've two tables

Table A: a_id,timemarker (datetime)

Table B: b_id,start (datetime), stop(datetime), cat(varchar)

table A

149|2010-07-19 07:43:45

150|2010-07-19 08:01:34

151|2010-07-19 07:49:12

table B

565447|2010-07-19 07:30:00|2010-07-19 08:00:00

565448|2010-07-19 08:00:00|2010-07-19 08:20:00

i want select all rows from Table A who are in the range of Table B

thanks

Answer 1

Select any A that is within ANY [B.start, B.end]

select a.*
from
table a 
where exists ( select * from table b where a.timemarker between b.start and b.stop)
;

The OP writes

i have trouble with my keys! the query executes very long. i have got in table a over 40k rows and in table b over 1.4 million rows... there is no relation within the tables – Norman 3 secs ago

Yes, because you're potentially comparing each A with every B = 40k * 1.4M comparisons.

But your question was "how do I do this", not "here's how I'm doing it, how can I make it faster".

if you want it faster, you'll need to add an index on B(start, end);