How do I join these recordsets

Results 1 to 3 of 3

Thread: How do I join these recordsets

  1. #1
    Laurence Blake Guest

    Default How do I join these recordsets

    Hi<BR>I&#039m trying to get up to speed fast with ASP so please execuse me if this is too basic!<BR><BR>I can successfully connect to and create a recordset against Index Server giving me:<BR>rsIndexServer : and in it are filename, path and characterization<BR><BR>I can also connect to and create a recordset against Access 97, giving me :<BR>rsAccessDB: and in it is salesrep, report_date, region and filename<BR><BR>The filename in both recordsets is the same.<BR><BR>Now I need to join these recordsets into one so I can print them.<BR>I understand the SELECT and INNER JOIN syntax what I don&#039t understand is the way to open the third recordset. What do I set the conn to?<BR><BR>All the " sqltext, conn" code examples I find seem to be based on<BR>opening data from ODBC or similiar data. How do I join them from already created recordsets?<BR><BR>Thanks in advance<BR><BR>Laurence<BR>

  2. #2
    Join Date
    Dec 1969

    Default Inter-DB Joins? No.

    &#062; Now I need to join these recordsets into one so I can print them.<BR><BR>A recordset is not a physical thing. It exists only temporarily in memory as the result of a query.<BR><BR>A "join" connects tables, or selected [via WHERE] portions of tables. But it is the SELECT statement at the outermost level that then helps produce the recordset.<BR><BR>[ And it&#039s a detail you probably don&#039t care about, but the term "RecordSet" applies *only* to ADO and the stuff that you use to connect to and manipulate the DB. Inside the DB, it is usually (but this depends on which DB you are using!) referred to as the "Result Set" or maybe the "Row Set", since it is truly a lie to call it a set of records, when it represents an in-memory version of the data that may or may not be reflected in actual records within the DB. ]<BR><BR>********************<BR><BR>&#062; I understand the SELECT and INNER JOIN syntax...<BR>&#062; what I don&#039t understand is the way to open the third <BR>&#062; recordset. What do I set the conn to?<BR><BR>And here is where you are hosed: You can&#039t *DO* cross-database JOINs. Simple as that, unfortunately.<BR><BR>In order to "import" a recordset (results of a query on one DB, remember, that simply resides in memory) into another DB, you&#039d have to create a table and dump the contents into the table. Way too slow. Much better to simply do two queries, where you use the results of the first one to drive the second one.<BR><BR>That is, extract all the filenames from the first query and use them to drive the second query.<BR><BR>Something like this:<BR><BR><BR>&#060;%<BR>&#039 rsIndexServer : recordset containing filename, path and characterization fields<BR>&#039<BR>conjunction = ""<BR>fnames = ""<BR>Do Until rsIndexServer.EOF <BR>&nbsp;&nbsp;&nbsp;&nbsp;fnames = fnames & conjunction & rsIndexServer("filename")<BR>&nbsp;&nbsp;&nbsp;&nb sp;conjunction = "&#039,&#039" &#039 apostrophe-comma-apostrophe<BR>&nbsp;&nbsp;&nbsp;&nbsp;rsIndexServe r.MoveNext<BR>Loop<BR>&#039<BR>SQL2 = "SELECT salesrep, report_date, region, filename FROM someAccessTable" _<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;" WHERE filename IN (&#039" & fnames & "&#039)"<BR>Response.Write "&#060;HR&#062;" & SQL2 & "&#060;HR&#062;" &#039 just for debugging!<BR>Set rsAccessDB = connectionToAccessDB.Execute(SQL2)<BR>...<BR>%&#06 2;<BR><BR>How it works: That sneaky code with the conjunction variable will concatenate all the filename fields found from first query into a string of the form<BR>&nbsp;&nbsp;&nbsp;&nbsp;foobar.html&#039,& #039zamboni.asp&#039,&#039xyz.txt<BR>so then you put it into the WHERE clause and your SELECT becomes<BR>&nbsp;&nbsp;&nbsp;&nbsp;SELECT fields FROM table WHERE filename IN (&#039foobar.html&#039,&#039zamboni.asp&#039,&#039 xyz.txt&#039)<BR>which is exactly what you want!<BR><BR><BR>

  3. #3
    Laurence Blake Guest

    Default RE: Inter-DB Joins? No. - Thanks

    Bill<BR><BR>Thank you so much for taking the time to answer my query with all the details and examples. Like a lot of other newbies I&#039m learning ASP by grabing bits of code from here and there and always trying to relate it back to Access.<BR><BR>This of course means you miss some of the key fundamentals along the way.<BR><BR>Thanks again, this problem had been bugging me for ages!!<BR><BR>Laurence Blake

Posting Permissions

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