Simple Select statement help

[sumlogic]

Hey SP,

Help!
I need to be able to select email addresses from a table named member based on 3 factor in another table

Factor 1: if alert=true
Factor 2: if the time is less than it is now
Factor 3: if the date is less than today's date

there is only 1 record currently that meets the first factor, so this record should be all I get no matter what.

the sql statement I am using looks like this:

SELECT member.email, tasks.alarm_me, tasks.alarm_date, tasks.alarm_time FROM tasks,member WHERE tasks.alarm_me='Y' AND tasks.alarm_date<=2005-2-02 AND tasks.alarm_time<='03:15:02'

this produces 22 results. So I think that this is pulling all records that meet the first factor, or the second, or the third. Is this right?

I am thinking that I should probably be using a join but know nothing about constucting a join statement, or if I am even correct in thinking about it.

Please help

Thanks

[r937]

you're missing a join condition and hence you are getting a cross join effect -- each row of the tasks table that meets all three conditions is joined to every member row

is there a column in tasks that can be used to match appropriate rows to the member table?

also, why are date and time in separate columns? the way that you have expressed factors 2 and 3, a time that is greater than it is now but from yesterday would be excluded!!

finally, you must put the date string into quotes

tasks.alarm_date<=2005-2-02 is equivalent to tasks.alarm_date<=2001

you want tasks.alarm_date<='2005-02-02'

[sumlogic]

r937,

Thanks for the reply, I just got it and it would have helped but I ended up doing this. I am testing the solution now and I think it works. I had to remove the time condition from the sql statement and test the time in php, because, if the date is any other day than today then the time doesn't really matter. And besides my logic was a little off. I only had to get results if they were past the current date and time. So the statement looks like this:

SELECT member.email, member.l_name, member.f_name, tasks.task_id, tasks.assigned_to, tasks.alarm_me, tasks.alarm_date, tasks.additional_notes, CONVERT(tasks.alarm_time,TIME), tasks.title FROM tasks,member WHERE member.member_id = tasks.assigned_to AND tasks.alarm_status='Pending' AND tasks.alarm_me='Y' AND tasks.alarm_date <=CURDATE()

I also just posted a problem about having to add minutes to the time of the returned record here. For instance 30 mins needs to be added to the record. Thinnk you could give me a hand with this. I searched the board to look for help using the ADDTIME function but there was only one post that didn't help much. The details are in the post....

Thanks alot!