Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    1

    Unanswered: Please Help - Access SQL JOIN problems

    Hi all,

    I hope someone can help me with this. I have an email manager which takes batches of emails and sends them out. Currently I have the following sql (which works) to limit the emails it sends to subscribers of a particular list.

    sql = "SELECT subscribers.* FROM subscribers LEFT JOIN " & _
    "(SELECT * FROM emailssent WHERE emailssent.batch = " & session("batch") & ") AS es ON subscribers.email = es.email " & _
    "WHERE (((es.email) Is Null)) and subscribers.unsub = -1 and subscribers.list = "&session("list")&" ;"
    Session("list") holds the list ID of the email list. Emailssent holds the emails that have already been sent in that batch (so if something goes wrong they are not sent to again).

    What I need to do (and have done for the rest of the site) is break the list out into a separate table. The new table is called subTo and contains subid (a reference to the id fied in the subscribers table) and listid (same thing but with the lists table). Now I need to add that on to the sql statement and only pull out those email addresses that are subscribed to certain lists.

    So, what I thought might work is the following:

    sql = "SELECT subscribers.* FROM subscribers LEFT JOIN " & _
    "(SELECT * FROM emailssent WHERE emailssent.batch = " & session("batch") & ") AS es ON subscribers.email = es.email LEFT JOIN " & _
    "subto ON subscribers.id = subto.subid " & _
    "WHERE (((es.email) Is Null)) and subscribers.unsub = -1 AND subto.listid = "&session("list")&";"
    Unfortunately it doesn't. I am getting a 'Syntax error (missing operator) in query expression ' error. Now I don't know whether the expression is totally wrong or I am just being let down by my bad syntax.

    Could someone please help me. I'm honestly not sure where to go next with this.

    All the best,

    Steve

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I think you're missing a closing parenthesis on your where clause. Have you tried a response.write(sql) to see what you're really trying to send?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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