mysql - SQL Query to Join tables between dates -

mysql - SQL Query to Join tables between dates -

i have mysql database records pager messages has 2 tables, 1 table messages , other table recipients, link between tables pagermessages.pcapcode = capcodes.bcapcode (a numerical address each pager message sent to).

this works semi-well, , typically utilize inner or left bring together show various pager messages , messages for.

the issue have, scheme dynamic, , pagers can re-programmed address 1, might have been recipient example1 may changed illustration 2.

this leads issue, want store messages in historical form, need way able specify between date range. thought have in capcodes table date & time (bstartdatetime) , finish date & time (bfinishdatetime) , in query able utilize work out how bring together pager messages.

this stuck though, select * pagermessages inner bring together capcodes on pagermessages.pcapcode = capcodes.bcapcode want able do same thing, pager message date (pdatetime) between bstartdatetime , bfinishdatetime (which blank) link, if outside of not link.

so take example

pager message table

id|pdatetime |pcapcode|pmessage 1 |2014-06-24 14:00|1 |this test message 2 |2014-06-24 15:00|1 |this test message 3 |2014-06-24 16:00|2 |this test message

capcode table

capcodeid|pcapcode|name |bstartdatetime |bfinishdatetime 1 |1 |example 1|2014-06-24 14:00|2014-06-24 14:30 2 |1 |example 2|2014-06-24 14:31| 3 |2 |example 3|2014-06-24 14:31|

in above examples, able bring together tables can table below:

id|pdatetime |pcapcode|name |pmessage 1 |2014-06-24 14:00|1 |example 1|this test message 2 |2014-06-24 15:00|1 |example 2|this test message 3 |2014-06-24 16:00|2 |example 3|this test message

so can see, because capcode exists twice in pcapcode table, has taken entry in date & time falls between, way can have multiple entries of same bcapcode in capcode table, , if changed add together date changed in capcodes table, , add together new entry rather having historical entries no longer accurate can't select them properly.

sorry if not create much sense, can clarify if needed think have covered of asking, kind of sql statement allow me above, not simple inner / left bring together can see.

thanks!

you can still bring together them. create sure utilize finish time if exists.

select a.id, a.pdatetime, b.name, a.pmessage pagemessage bring together capcode b on a.pcapcode = b.pcapcode , b.bstartdatetime <= a.pdatetime , (b.bfinishdatetime null or a.pdatetime <= b.bfinishdatetime);

mysql sql