How to link 2 SQL statements..........

[davidgordon]

Hi,

Quick question: How can I link 2 SQL statements in my asp page.
Originally I had 1 statement, but because it was fairly complex, it
took too long to parse.

SQL statement 1

strQuery = "Select ..............

Set RS = adoDataConn.Execute(strQuery)

then

Create SQL statement 2

strQuery2 = "SELECT stockmovements.JobNumber,
stockmovements.InStockMarker, orderlines.JobNumber"
strQuery2 = strQuery2 & " FROM stockmovements INNER JOIN orderlines ON
stockmovements.JobNumber = orderlines.JobNumber"
strQuery2 = strQuery2 & " WHERE (((stockmovements.JobNumber)=" &
RS("JobNumber") & "));"

(''''' RS("JobNumber") is pulled from SQL statement 1)


Set RS_Marker = adoDataConn.Execute(strQuery2)

_______________________________

When running the asp page, I want to pull out the field
stockmovements.InStockMarker (SQL statement 2) for each record in the
loop provide by SQL statement 1. I have tried, but all I get is
stockmovements.InStockMarker for the first record of SQL statement 1
listed for all the records.

Do I need to place a Do Loop within a Do Loop ???

Appreciate your help

Thanks

David.

 

[CJM]

Look up the FETCH instruction in Books Online. It will allow you to loop
through the first set of rows, and run a separate query on each. I imagine
you could collate the results in a temp table...

I would be careful though. This is not necessarily the most efficient
approach. If you published your data structure and what you are trying to
acheive, we might be able to think of a better all-in-one query...

Chris