Nested queries in

Results 1 to 2 of 2

Thread: Nested queries in

  1. #1
    Join Date
    Dec 1969

    Default Nested queries in

    I&#039;ve worked with ASP 2.0 and 3.0 for the past 4 years, and am finally learning I&#039;ve worked through the database tutorials in the .net SDK, but they don&#039;t demonstrate how to setup a nested query on a single page.<BR><BR>What do I mean by a nested query? Here is a simplified version of the code I&#039;d like to convert from asp 3.0 to<BR><BR>&#060;%SQL = "SELECT * from Companies"<BR>set rs = DB.execute(SQL)<BR>do while not rs.eof%&#062;<BR> &#060;b&#062;&#060;%=rs("CompanyName")%&#062;&#060 ;/b&#062;<BR><BR> &#060;%SQL2 = "SELECT * from Contacts WHERE CompanyID = " & rs("CompanyID")<BR> set rs2 = DB.execute(SQL2)<BR> do while not rs2.eof%&#062;<BR> &nbsp;&nbsp;<BR> &#060;%=rs2("FirstName") & " " & rs2("LastName")%&#062;<BR><BR> &#060;%rs2.movenext<BR> loop<BR> rs2.close : set rs2 = nothing%&#062;<BR> &#060;%rs.movenext<BR>loop<BR>rs.close : set rs = nothing%&#062;<BR><BR>Thanks, if you can offer any information on how to implement this in an .ASPX page.

  2. #2
    Join Date
    Dec 1969

    Default incredibly inefficient code

    You should do this with one query.<BR><BR>Select Companies.CompanyID,<BR> Companies.CompanyName,<BR> Contacts.FirstName,<BR> Contacts.LastName<BR> from Companies,Contacts<BR> Where Companies.CompanyID = Contacts.CompanyID<BR> ORDER BY CompanyID ASC<BR><BR> Results<BR> <BR> 1 IBM Bob Smith<BR> 1 IBM Sue Jones<BR> 2 SUN Lucie Goosey<BR> 3 ATT Sharon Stone<BR><BR> Then, as you iterate through the recordset, keep<BR> track of the current CompanyID versus the last CompanyID.<BR> When it changes, write out the company name.<BR><BR> You can almost always avoid this business of what you<BR> call nested queries. You&#039;ll notice a huge gain in<BR> performance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts