[SOLVED] Selecting entry from mySQL based on date range

[amites]

This one is kicking my butt,

 

I am building a query with a join statement, the idea is that all rows with an entry within a certain range of another table entries, I know i can do this in a single query but I'm having trouble with it

 

don't want to pull things out, build my range and then query again...

 

anyway the code so far looks like:

 

$query = "SELECT s.id, s.userid, s.msg_date, s.subject"
. "\n FROM bil_msg_sent AS s"
. "\n JOIN bil_msg_look AS l"
. "\n WHERE s.locid = l.locid"
. "\n AND s.msg_date BETWEEN **l.msg_date - 1 hour** AND **l.msg_date + 1 hour**";

 

obviously the parts in the ** are my comments describing what I want to do,

 

any ideas?  ???

[toplay]

$query = "SELECT s.id, s.userid, s.msg_date, s.subject

  FROM bil_msg_sent AS s

    JOIN bil_msg_look AS l ON s.locid = l.locid

                WHERE

        s.msg_date BETWEEN l.msg_date - INTERVAL 1 hour AND l.msg_date + INTERVAL 1 hour

            ";

 

[amites]

had to tinker with other parts of it, though your BETWEEN line worked perfectly

 

many thanks