mltiple recordset - join or data shaping?

Results 1 to 2 of 2

Thread: mltiple recordset - join or data shaping?

  1. #1
    Join Date
    Dec 1969

    Default mltiple recordset - join or data shaping?

    I need to create a query within a query. I&#039ve read up on Data shaping and table joins but I don&#039t know if either are more efficient than what I have now or if there&#039s something better - using arrays for example.<BR>Basically I have this<BR><BR>for outerquery = 1 to 10<BR>SQL = "Select * From employees where " (conditions based on for/next)<BR>set rs1 = conn.execute(SQL)<BR>do while not rs1.eof<BR>*** now I must use fields from this recordset to create a value and that indexes a value in a different table ***<BR>floor=rs1("floor")<BR>group=rs1("group")<BR >NEWSQL=Select * From groups where group =" floor & "-" & group<BR>*** the indexkey for table "groups" is above (ie "10-67" etc)<BR>set rs2=conn.execute(NEWSQL)<BR>Response.Write(rs1("em ployeename") & " with " & rs2("Manager"))<BR>loop<BR>next<BR><BR>Is this the optimal method? Mind you, the actual tables I&#039m using are quite large 5000+ records in the first a few hundred the later. many fields in each And the above code is not the actual, there are a few more tests and so forth.<BR><BR>I&#039m new to SQL and would appreciate anyone&#039s advice.

  2. #2
    Join Date
    Dec 1969

    Default RE: mltiple recordset - join or data shaping?

    If you are dealing with large amounts of data across multiple tables I would use a view and then use a stored procedure to select from the view.<BR><BR>What you are doing above is very inefficient in the sense that you are doing one select for each record in another recordset.<BR><BR>If you want some more help with the actual SQL statement then let me know a bit more about the table relationships, because it looks like the tables relate and you are not using this.<BR><BR>Hope this helps a little bit or gives you an idea of what to research<BR>James

Posting Permissions

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