By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,255 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Sep 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

<da*********@scene-double.co.uk> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
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.

Sep 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.