Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: SQL Select statement within a Select statement

Results 1 to 3 of 3
Share This Thread →
  1. #1
    phoenixaz is offline Contributing User
    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
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,535
    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

  3. #3
    phoenixaz is offline Contributing User
    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.

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

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

ASP Free Advertisers and Affiliates