#1
  1. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30

    SQL Select statement within a Select statement


    The code below works just fine in an asp emailer as it loops through the DB. A BIG thank you goes out to BigMike1212 for helping me with this code.

    Code:
    strSQL = "SELECT id,name, email, max(issueddate) AS max_date FROM (SELECT table1.id,table1.name as name, table1.email AS email, table2.issueddate AS issueddate FROM table1 INNER JOIN table2 ON table1.id=table2.id WHERE table1.remove is null and (table1.id >= '"& id1 &"' and table1.id <= '"& id2 &"') and month(table2.issueddate) between month('" & dDateFrom & "') and month('" & dDateTo & "'))  AS a GROUP BY id, name, email ORDER BY 1"
    I now want to add one more condition from another table (table3) and can't figure out how to add another select statement to the above code. I want to add:

    Code:
    SELECT SUM (Credits) from Table3 where date > '1/1/2012'
    in addition to where the SUM of id = '0' (id is in table 1,2 and 3) (id equals the customer id)

    What I want to accomplish is for emails to be sent to all in the very top code except for customers that have already made a purchase.

    I would appreciate your help. Thanks
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,592
    Rep Power
    278
    try that
    Code:
    strSQL = "SELECT a.id, a.name, a.email, max(a.issueddate) AS max_date, SUM(b.Credits) as credits FROM (SELECT table1.id,table1.name as name, table1.email AS email, table2.issueddate AS issueddate FROM table1 INNER JOIN table2 ON table1.id=table2.id WHERE table1.remove is null and (table1.id >= '"& id1 &"' and table1.id <= '"& id2 &"') and month(table2.issueddate) between month('" & dDateFrom & "') and month('" & dDateTo & "'))  AS a join from Table3 as b on b.id=a.id and date > '1/1/2012' GROUP BY id, name, email ORDER BY 1"
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Feb 2008
    Posts
    790
    Rep Power
    30
    Thank you for that GK.

    I am getting this error:

    Incorrect syntax near the keyword 'from'.

    I appreciate your efforts.

Similar Threads

  1. Select statement help>?
    By elviscat in forum ASP Development
    Replies: 3
    Last Post: November 13th, 2009, 12:40 AM
  2. Using IF statement INSIDE a SELECT statement
    By K_Snyder in forum Microsoft SQL Server
    Replies: 2
    Last Post: June 10th, 2009, 01:47 PM
  3. Multuple select drop down post to select statement
    By wangxx in forum ASP Development
    Replies: 5
    Last Post: March 8th, 2009, 07:38 PM
  4. Pre select option in select statement
    By asp_dev in forum ASP Development
    Replies: 11
    Last Post: July 18th, 2007, 08:57 AM
  5. select statement help
    By papaluco in forum Microsoft SQL Server
    Replies: 1
    Last Post: December 11th, 2003, 03:13 PM

IMN logo majestic logo threadwatch logo seochat tools logo