Query a Recordset

[dmh4ab]

This sounds simple.
I pull a recordset from a SQL database. The query for the recordset follows:

SELECT tbl_RepairSummary.BasicPartNumber, tbl_RepairSummary.RoutingCode, tbl_RepairSummary.RevLevel, tbl_RepairSummary.RevDate, tbl_RepairSummary.PartName, tbl_RepairSummary.Summary, tbl_RepairSummary.Comments, tbl_RepairSummary.Preparedby, tbl_RepairSummary.SubsDocNumber, tbl_RepairSummary.NonHoneywellProduct, tbl_RtgDash.DashNo, tbl_RtgDash.ModToPN, tbl_RtgDash.ModToDash, tbl_RepairSummary.InProcess, tbl_RepairSummary.Filename, tbl_RepairSummary.Inactivated
FROM tbl_RepairSummary LEFT JOIN tbl_RtgDash ON (tbl_RepairSummary.RevLevel = tbl_RtgDash.RevLevel) AND (tbl_RepairSummary.RoutingCode = tbl_RtgDash.RoutingCode) AND (tbl_RepairSummary.BasicPartNumber = tbl_RtgDash.BasicPartNumber);

I now need to use this as a source to select data from. I've tried multiple ways of just dropping this into another query, but none of them return the data I need. Is there any way I can just simply use the above recordset as the source of another recordset??

Thanks,
dmh4ab

 

[GaryC123]

What do u mean as the source of another recordset? Use the fields in it to query the database again?

http://www.intouch-media.co.uk

 

[dmh4ab]

I need to query the results of the recordset. The first recordset gives me the matches I need from the two tables in the database. Then I need to narrow those results down even further.
It's kind of a complex query, but I'll be glad to try to explain it further if I need too. (Didn't want to overload everyone here with too much unneccessary info.)

Thanks,
dmh4ab

 

[cybercop23]

I know this is a simple question, but have you tried another LEFT JOIN from the main table to the 3rd table that has your data. Or you may want to try to do a sub select. select from the 3rd table where (your select statement from above)

Hope this works for you.

 

[GaryC123]

What is it from Table 1/Table 2 is gonna be the match on Table 3

http://www.intouch-media.co.uk

 

[dmh4ab]

BasicPartNumber, RoutingCode, and RevLevel are the fields that should match. It's a one (tbl_RepairSummary) to many (tbl_RtgDash) relationship between the tables.

Can I save the results of the query into a table? If I could do this somehow, it think it would achieve what I need. But, I'm not sure if or how I would do this in SQL.

Thanks,
dmh4ab

 

[cbokowski]

To use the result set of one query in another, you could do something along this line. You create a temp table on the first query and then join the temp table to the 3rd table. The times I have had to use this I put it in a stored procedure. The "#" is key in the temp tables name. temporary tables is covered in sql books on line(bol) if you need more infomation.

set nocount on;
Select table1.Field1, Table2.Field2
INTO #tmp
from table1
inner join Table2 on ......

select #tmp.Field1, #tmp.Field2, table3.field3
from #tmp
inner join table3 on .....

drop table #tmp


I hope this helps
c

 

[jackfl]

Take your complex query with all it's joins and store it as a SQL View; then treat this View as just another SQL Table.

 

[Tarwn]

The only problem with SQL views is that they are not universally inmplemented by databases, so you will need to check if your version of your particular database actually supports them.

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

 

[spiral123]

jackfl, do you know where I could get some info on storeing a query as a sql view it sounds exactly like what I need

 

[cbokowski]

here is a sql server link for creating views

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp