SQL Select statement within a Select statement

[phoenixaz]

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.

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:

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

[gk53]

try that

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"

[phoenixaz]

Thank you for that GK.

I am getting this error:

Incorrect syntax near the keyword 'from'.

I appreciate your efforts.