recordset Question

[nawrioj]

Is it possible to pull a recordset from the database and adding new recordset to the previous recordset.

So it's going to be like

recordset1 + recordset2 = newrecordset

If this possible can anybody show me the simple way to do this ??

thanks
nawrioj

 

[Catadmin]

Need a little more detail please. Are you pulling recordsets from the same table and trying to merge them or are you pulling them from different tables and trying to create a new table with both recordsets inserted into various fields on the new recordset?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[nawrioj]

I'm pulling the recordset from the same table.

for example :
recordset 1 has the query of

select *
from categories
where name like %brush%

recordset 2 has the query of

select *
from categories
where name like %blue%

and I want to merge these 2 recordsets

Thanks
nawrioj

 

[Catadmin]

My first thought is to recommend creating a view with a self join.

Select * from categories c1 join categories c2
on c1.(primary key) = c2.(primary key)
where name like '%brush%' or name like '%blue%'

Try this out and see if it helps.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Pattycake245]

Is a join really needed? Depending on what you are doing with this data wouldn't a simpler select like this suffice?

Select * from categories
where name like '%brush%' or name like '%blue%'
order by name

Tim

 

[Catadmin]

I only recommend a join because he wants to have the two recordset become a new one, which indicates to me that he wants it all reading into the same row.

Of course, I might have completely misunderstood the request.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Pattycake245]

I see your logic Catadmin, I guess what needs to be known is the bigger picture. A union of the two queries would also serve this prupose. I hope you don't think I'm ripping apart your post as I am nowhere near an expert. In a case like this, I post in part, to increase my own knowledge. Maybe nawrioj can let us know what he is trying to do.

Tim

 

[Catadmin]

Pattycake245, I agree that more details about what he actually wants to do with the data would definately help.

Nawrioj, are you trying to pull this info into a new table for any purpose, create a view for users, or are you literally trying to insert a new record at the bottom of the current table with all/some fields of the combined records?

The later will not work if you're trying to pull all fields from both types into one line without a radical redesign of your current table. It might not work at all. Also, is there another conditional you're using to match up all the Blues and Brushes? I mean, how do you know which Blue should go with which Brush in the final recordset?

And how many records of type Brush and type Blue are you expecting anyway? One of each or several?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[nawrioj]

i guess I haven't really provide too much detail about the problem that I'm encountering but thanks for the respond though.

I'm trying to do "any words search" so if a user enter "tooth brush" the code will search for keyword tooth and brush. However, My code below seems to give me only the result for the last keyword. Its really obvious why it does that since I'm using the for loop so the new recordset is overwriting the old recordset.

So my problem is that I want to merge both of the result together in one recordset.

btw, I use stored procedure for my query.

Sorry for the lack of clarity. I hope I give enough clarity now . Thanks people

here is the source code
if (searcht="") then
If len(Request.QueryString("categoryid")) = 0 Then
Set rsCategories = Nothing
Set objUtil = Nothing
Response.Redirect("index.asp")
Else
strSQL = "selectProducts 0, " & Request.QueryString("categoryid")
End If
set rsProducts = objUtil.Execute_ADO_Query(strSQL)
if (rsProducts.RecordCount <> 0) then
rsProducts.MoveFirst
strLastManufacturerName = ""
do while (not(rsProducts.EOF))
if (not(isnull(rsProducts("metakeywords")))) then
strMetaKeywords = strMetaKeywords & "," & rsProducts("metakeywords")
end if
rsProducts.MoveNext
loop
rsProducts.MoveFirst
end if
else
'********************************************************************************************
' Make variation of Query here
'********************************************************************************************
searchterm = trim(searcht)
sterm = Split(searchterm)
for foo = 0 to Ubound(sterm)
strSQL = "selectSearchProducts '" & sterm(foo) & "', 0"
set rsProducts = objUtil.Execute_ADO_Query(strSQL)
if (rsProducts.RecordCount <> 0) then
rsProducts.MoveFirst
strLastManufacturerName = ""
do while (not(rsProducts.EOF))
if (not(isnull(rsProducts("metakeywords")))) then
strMetaKeywords = strMetaKeywords & "," & rsProducts("metakeywords")
end if
rsProducts.MoveNext
loop
rsProducts.MoveFirst
end if
Next
end if