Results 1 to 2 of 2
  1. #1
    Join Date
    May 2001
    Posts
    51

    Unanswered: Help on strange qwuery. Nulls?

    Hi all, let me explain my problem firsty, maybe there is a better way to solve it than the method im using.

    I have an image gallery script, users can add comments to each image (forming a thread, i.e just like a thread in a buletin board) below the picture of the image.

    Now, if a reply is posted to the thread, i'd like to email all users who have a comment within the thread that there is a reply, but only if a previous email for the same thread hasn't been sent since there last visit to the gallery.

    I.e UserA posts a commment and goes away, while gone, UserB and UserC both reply to his comment (the thread is now 3 comments long, lets assume UserB replies first).

    When UserB replies, I want UserA to be sent an email saying there is a reply to his comment. I dont want a second email to be sent (due to UserC's reply) unless it occured after UserA saw the reply posted by UserB, ahh, so very confusing

    Ok, lets hope the technical side can clarify the situation. My comment table is composed like this:

    | Comment ID | imageID | userID | Comment | date |

    To record when a user is emailed, i have a second table, emaillog.

    | logID | imageid | userid |

    The basic idea is, when a comment is posted, i firstly grab all the userid who have posted replies to that thread (they all have the same imageid). I then check the emaillog to see if there are any users who have already had emails sent to them (if they have, there userid and corresponding imageid will be in the emaillog). If I cant find a record in the emaillog, i email them and then add a record, if I do find a record I dont email them.

    Assuming that i handle the emaillog clearup afterwards, im having trouble forumlating a query that will returns the userids of all users who need to be emailed based solely on the imageid.

    I have the following:

    SELECT DISTINCT c.userid
    FROM emaillog e LEFT JOIN comment c ON c.imageid=e.imageid
    WHERE c.imageid='someimageid' AND c.userid != e.userid
    This will return the userids ONLY is there is atleast 1 matching record in the emaillog, otherwise it returns an empty set. What am i doing wrong? Someone please help me.

    Taz

  2. #2
    Join Date
    Jun 2003
    Posts
    21

    Re: Help on strange qwuery. Nulls?

    Originally posted by etones
    Hi all, let me explain my problem firsty, maybe there is a better way to solve it than the method im using.

    I have an image gallery script, users can add comments to each image (forming a thread, i.e just like a thread in a buletin board) below the picture of the image.

    Now, if a reply is posted to the thread, i'd like to email all users who have a comment within the thread that there is a reply, but only if a previous email for the same thread hasn't been sent since there last visit to the gallery.

    I.e UserA posts a commment and goes away, while gone, UserB and UserC both reply to his comment (the thread is now 3 comments long, lets assume UserB replies first).

    When UserB replies, I want UserA to be sent an email saying there is a reply to his comment. I dont want a second email to be sent (due to UserC's reply) unless it occured after UserA saw the reply posted by UserB, ahh, so very confusing

    Ok, lets hope the technical side can clarify the situation. My comment table is composed like this:

    | Comment ID | imageID | userID | Comment | date |

    To record when a user is emailed, i have a second table, emaillog.

    | logID | imageid | userid |

    The basic idea is, when a comment is posted, i firstly grab all the userid who have posted replies to that thread (they all have the same imageid). I then check the emaillog to see if there are any users who have already had emails sent to them (if they have, there userid and corresponding imageid will be in the emaillog). If I cant find a record in the emaillog, i email them and then add a record, if I do find a record I dont email them.

    Assuming that i handle the emaillog clearup afterwards, im having trouble forumlating a query that will returns the userids of all users who need to be emailed based solely on the imageid.

    I have the following:



    This will return the userids ONLY is there is atleast 1 matching record in the emaillog, otherwise it returns an empty set. What am i doing wrong? Someone please help me.

    Taz
    select comment.userid from emaillog left join comment on comment.userid<>emaillog.userid where comment.imageid=an_image_id

    returns all the userid's from Comment that are not in emaillog (that have not yet had an email).
    Last edited by Niels; 06-17-03 at 15:38.

Posting Permissions

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