Advanced SQL Problem

Dear All,

I have 2 retreive some data from 2 tables that are linked together, however I cannot manage to do it.

My first table has three fields that are relevant to me and these are NAME,USERPASS and ACCOUNT, and the second table has CODE and EMAIL. ACCOUNT from the first table and CODE from the second table are linked together.

Now what I want to do is retreive the NAME AND USERPASS from the first table where the ACCOUNT is the same as the CODE and then send out an e-mail with the username and password.

I tried this query, however it is not working:-

SELECT XERSITES.ACCOUNT, STKCST.CODE, XERSITES.NAME, XERSITES.USERPASS, STKCST.EMAIL
FROM XERSITES INNER JOIN STKCST ON XERSITES.ACCOUNT = STKCST.CODE;

Can you please help me out?

Thanks for your time

Ok i have discovered that I cannot use an inner join since the driver does not support it! I have to do a nested loop

I have come up with this code:-

Set rsSelect=Server.CreateObject("ADODB.Recordset")
rsSelect.ActiveConnection = cnnDFlex

Set rsSelectSTK=Server.CreateObject("ADODB.Recordset")
rsSelectSTK.ActiveConnection = cnnDFlex

sqlSelect="SELECT name, userpass, account FROM xersites WHERE EMAIL <> ''"
rsSelect.Open (sqlSelect)
rsSelect.movefirst
do
sqlSelectSTK="SELECT code, email FROM stkcst WHERE EMAIL <> ''"
rsSelectSTK.Open (sqlSelectSTK)
rsSelectSTK.movefirst
do
if rsSelect("account") = rsSelectSTK("code") then
sqlInsertIntoTemp="insert into temp(name,userpass,email) values(" & rsSelect("Name") & ",'" & rsSelect("userpass") & "','" & rsSelectSTK("Email") & "')"
set rsInsertIntoTemp=cnnDFlex.execute(sqlInsertIntoTem p)
rsSelectSTK.movenext
loop until rsSelectSTK.eof
end if
rsSelect.movenext
loop until rsSelect.eof

but it is giving me an error:-

Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'
/isl/admin/createtemptable.asp, line 42
loop until rsSelectSTK.eof


Please can you help me out?

Count up your "do" statements and "loop" statements, I think you are short one loop

Ok I went for another approach and it did work:-

Set rsSelect=Server.CreateObject("ADODB.Recordset")
rsSelect.ActiveConnection = cnnDFlex
'sqlSelect="SELECT * FROM xersites WHERE EMAIL <> ''"
sqlSelect="SELECT DISTINCT STKCST.CODE, XERSITES.ACCOUNT, XERSITES.NAME, XERSITES.USERPASS, STKCST.EMAIL"
sqlSelect = sqlselect & " FROM XERSITES, STKCST WHERE XERSITES.ACCOUNT = STKCST.CODE"
'sqlSelect = sqlselect & " AND STKCST.EMAIL = 'jmontfort@hotmail.com'"
sqlSelect = sqlSelect & " AND STKCST.EMAIL <> ''"
rsSelect.Open (sqlSelect)

Thanks for your help