SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    doberman
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple Select -SOLVED

    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:
    Code:
    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
    Last edited by sumlogic; Feb 3, 2005 at 15:40. Reason: Solved the issue
    Got logical business solutions? Get SuM!
    Greg Starr (SuM)
    LogicStation.net
    Florida Based Contract Web Developer

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2005
    Location
    doberman
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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!
    Got logical business solutions? Get SuM!
    Greg Starr (SuM)
    LogicStation.net
    Florida Based Contract Web Developer


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •