ADVANCED SQL PROBLEM 1

[JohannIcon]

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

 

[JohannIcon]

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

 

[JohannIcon]

I have come up with this code:-

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

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

sqlSelect=&quot;SELECT name, userpass, account FROM xersites WHERE EMAIL <> ''&quot;
rsSelect.Open (sqlSelect)
rsSelect.movefirst
do
sqlSelectSTK=&quot;SELECT code, email FROM stkcst WHERE EMAIL <> ''&quot;
rsSelectSTK.Open (sqlSelectSTK)
rsSelectSTK.movefirst
do
if rsSelect(&quot;account&quot = rsSelectSTK(&quot;code&quot then
sqlInsertIntoTemp=&quot;insert into temp(name,userpass,email) values(&quot; & rsSelect(&quot;Name&quot & &quot;,'&quot; & rsSelect(&quot;userpass&quot & &quot;','&quot; & rsSelectSTK(&quot;Email&quot & &quot;')&quot;
set rsInsertIntoTemp=cnnDFlex.execute(sqlInsertIntoTemp)
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?

 

[Tarwn]

How about:
&quot;SELECT XERSITES.ACCOUNT, STKCST.CODE, XERSITES.NAME, XERSITES.USERPASS, STKCST.EMAIL
FROM XERSITES, STKCST WHERE XERSITES.ACCOUNT = STKCST.CODE;&quot;



_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[JohannIcon]

Hi Tarwn,

Thanks mate, it worked perfectly!!!