multiple db query - join recordsets?

Greetings again.

I am trying to search through a few tables (really just two tables but
I'm joining a few others in the process because of how I want to
display the results).

I broke this into 2 select statement cause I couldn't get it to work as
one. Ideally I'd like to have it as one select statement because I
want to be able to page my results.

Here it is...

'SEARCH THROUGH PAGEINFO DB-------------------------------------------
MySQL_1 = "SELECT
pageInfo.productID,pageInfo.pageID,pageInfo.pageTitle,pageIn fo.pageData,
" _
&
"meta.pageID,meta.productID,meta.metaTitle,metaDesc,products .productID,products.listName
FROM pageInfo,meta,products " _
& "WHERE products.productID=pageInfo.productID " _
& "AND pageInfo.pageID=meta.pageID AND
pageInfo.productID=meta.ProductID " _
& "AND pageData LIKE '%"&session("SearchCriteria")&"%'"
Set rs1 = MyConn.execute (MySQL_1,1,1)
if rs1.eof then
rs1.close
set rs1 = nothing


'spilt up words and then search
again------------------------------------------
MySQL_1 = "SELECT
pageInfo.productID,pageInfo.pageID,pageInfo.pageTitle,pageIn fo.pageData,
" _
&
"meta.pageID,meta.productID,meta.metaTitle,metaDesc,products .productID,products.listName
" _
& "FROM pageInfo,meta,products "
'MySQL = "SELECT * FROM " & prodTable
searchWords = trim(session("SearchCriteria"))

if len(searchWords)>0 then
searchWords = replace(searchWords, "," , " ")
if instr(searchWords," ")>0 then
'more than one word---------------------------
txtWords = split(searchWords)
MySQL_1 = MySQL_1 & " WHERE
products.productID=pageInfo.productID " _
& "AND pageInfo.pageID=meta.pageID AND
pageInfo.productID=meta.ProductID " _
& "AND "
orWord = ""
for i = 0 to ubound(txtWords)
if i > 0 then orWord = " OR "
MySQL_1 = MySQL_1 & orWord & " pageData LIKE '%" &
txtWords(i) & "%'"
next
else
'only one word------------------------------
MySQL_1 = MySQL_1 & "WHERE pageData LIKE '%"&searchWords&"%'"
end if

end if

Set rs1 = MyConn.execute (MySQL_1,1,1)

end if

'SEARCH THROUGH SYSTEMS DB---------------------------------
MySQL = "SELECT systemID,systemName,systemInfo,metaTitle,metaDesc FROM
systems WHERE systemInfo LIKE '%"&session("SearchCriteria")&"%'"
Set rs2 = MyConn.execute (MySQL,1,1)
if rs2.eof then
rs2.close
set rs2 = nothing


'spilt up words and then search
again------------------------------------------
MySQL = "SELECT systemID,systemName,systemInfo,metaTitle,metaDesc
FROM systems "
'MySQL = "SELECT * FROM " & prodTable
searchWords = trim(session("SearchCriteria"))

if len(searchWords)>0 then
searchWords = replace(searchWords, "," , " ")
if instr(searchWords," ")>0 then
'more than one word---------------------------
txtWords = split(searchWords)
MySQL = MySQL & " WHERE "
orWord = ""
for i = 0 to ubound(txtWords)
if i > 0 then orWord = " OR "
MySQL = MySQL & orWord & " systemInfo LIKE '%" &
txtWords(i) & "%'"
next
else
'only one word------------------------------
MySQL = MySQL & "WHERE systemInfo LIKE
'%"&session("SearchCriteria")&"%'"
end if

end if

Set rs2 = MyConn.execute (MySQL,1,1)

end if

Now with that all said, is there a way I can combine the two recordsets
into one?

Thanks,

Joe

Re: multiple db query - join recordsets?

joe wrote:
> Greetings again.
>
>
> Now with that all said, is there a way I can combine the two
> recordsets into one?
>
Please show us sample data (in tabular form) followed by intended results
(also in tabular form)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: multiple db query - join recordsets?

pageInfo table 'table does have a primary key of ID
pageID pageTitle pageData
productID
============================================================ ===============
home Home blah, blah, blah
product1
faqs FAQ'S blah, blah, blah
product1
how_works How it Works blah, blah, blah
product1
home Home blah, blah, blah
product2
faqs FAQ'S blah, blah, blah
product2
how_works How it Works blah, blah, blah
product2


meta table 'also has a primary key as ID
pageID metaTitle metaDesc
productID
============================================================ ================
home blah, blah, blah blah, blah, blah
product1
faqs blah, blah, blah blah, blah, blah
product1
how_works blah, blah, blah blah, blah, blah
product1
home blah, blah, blah blah, blah, blah
product2
faqs blah, blah, blah blah, blah, blah
product2
how_works blah, blah, blah blah, blah, blah
product2


products table 'also has a primary key and a bunch of other fields
productID listName
=========================
product1 green widget
product2 blue widget
product3 red widget
product4 gray widget
product5 black widget

Here is the how I am trying to do my magic. I have one products.asp
page that I am using to display all of my various page/site data.

my url's look like this...

products.asp?productID=product1&pageID=Home
products.asp?productID=product1&pageID=how_it_works

etc

Here's the other side. I have a 'systems page that is getting info
from my systems table

systems table
systemID systemName systemInfo
============================================================ ======
system1 wide widget system blah, blah, blah
system2 small widget system blah, blah, blah
system3 thin widget system blah, blah, blah


I want to be able to search through both the
pageInfo table/pageData field
and...
systems/systemInfo field

my system url's look like this...
systems/info.asp?system=system1
systems/info.asp?system=system2

I'd welcome any db design feedback along with code pointers.

Thanks!

Re: multiple db query - join recordsets?

Shoot! I forgot to ask what database you are using.
joe wrote:
> pageInfo table 'table does have a primary key of ID
> pageID pageTitle pageData
> productID
>
============================================================ ===============
> home Home blah, blah, blah
> product1
> faqs FAQ'S blah, blah, blah
> product1
> how_works How it Works blah, blah, blah
> product1
> home Home blah, blah, blah
> product2
> faqs FAQ'S blah, blah, blah
> product2
> how_works How it Works blah, blah, blah
> product2
>
>
> meta table 'also has a primary key as ID
> pageID metaTitle metaDesc
> productID
>
============================================================ ================
> home blah, blah, blah blah, blah, blah
> product1
> faqs blah, blah, blah blah, blah, blah
> product1
> how_works blah, blah, blah blah, blah, blah
> product1
> home blah, blah, blah blah, blah, blah
> product2
> faqs blah, blah, blah blah, blah, blah
> product2
> how_works blah, blah, blah blah, blah, blah
> product2
>
>
> products table 'also has a primary key and a bunch of other fields
> productID listName
> =========================
> product1 green widget
> product2 blue widget
> product3 red widget
> product4 gray widget
> product5 black widget
>
> Here is the how I am trying to do my magic. I have one products.asp
> page that I am using to display all of my various page/site data.
>
> my url's look like this...

I'm baffled as to why this is relevant ...


You said you wanted to create a single recordset for your results. Given
your sample data above, what do you want the resulting recordset to contain
(in tabular format please)? If I've missed this in the text I snipped, my
apologies, but I did not recognize it so just point me at it. Here is a
template:

I wish to supply x,..., and y as parameters. Given the sample data above, I
would like to create a query that will return the following results in a
recordset


Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: multiple db query - join recordsets?

I am basically searching through two fields (pageInfo and systemsInfo)

-> pageData from my access pageInfo table

-> systemInfo from my systems table

The data from the systems table is straight forward, I just want to
return data (no joins from any other table).

The data from my pageInfo table has a few joins/conditions..

sql = "SELECT pageInfo.productID,pageInfo.pageID,pageInfo.pageTitle," _
& "pageInfo.pageData, " _
& "meta.pageID,meta.productID,meta.metaTitle,meta.Desc," _
& "products.productID,products.listName " _

& "FROM pageInfo,meta,products " _

& "WHERE products.productID=pageInfo.productID " _
& "AND pageInfo.pageID=meta.pageID " _
& "AND pageInfo.productID=meta.ProductID " _
& "AND pageData LIKE '%"&session("SearchCriteria")&"%'"

I am not sure how to display my results in a tabular form. I think
that I realized that I cannot join these record sets casue they do not
share the same data...


'here are the results say I search for 'green'

productID pageTitle pageData
============================================================ ====
product1 Home this is a nice green widget
product2 How it Works can be used with a green widget

'and now for the systems table results

systemID systemName systemInfo
============================================================ ===============

system1 Cool Green widgets things to do with green
widgets




I'm figuring that I'd display the data like...

-----------------------------------------
product: product1

Page/System Name: Cool Green widgets

Data:
things to do with green widgets
-----------------------------------------
product: product1

Page/System Name: Home

Data:
this is a nice green widget
-----------------------------------------
product: product2

Page/System Name: How it Works

Data:
can be used with a green widget
-----------------------------------------

Does this make any sense?

Thanks!

Re: multiple db query - join recordsets?

joe wrote:
> I am basically searching through two fields (pageInfo and systemsInfo)
>
> -> pageData from my access pageInfo table
>
> -> systemInfo from my systems table
>
> The data from the systems table is straight forward, I just want to
> return data (no joins from any other table).
>
> The data from my pageInfo table has a few joins/conditions..
>
> sql = "SELECT pageInfo.productID,pageInfo.pageID,pageInfo.pageTitle,"
> _ & "pageInfo.pageData, " _
> & "meta.pageID,meta.productID,meta.metaTitle,meta.Desc," _
> & "products.productID,products.listName " _
>

Why are you returning the same data multiple times? Given your join
conditions, productID will contain the same information regardless of
whether it's coming from products or pageInfo ... Oh wait, the productID is
not used in your join between pageInfo and meta. is that deliberate? Can
related records in these tables hold different productID's? If so, you
needed to join products to meta in this query. I suspect you should have
included productID in the pageInfo to meta join. I will show both versions
below. The same goes for pageID.

> & "FROM pageInfo,meta,products " _
>
> & "WHERE products.productID=pageInfo.productID " _
> & "AND pageInfo.pageID=meta.pageID " _
> & "AND pageInfo.productID=meta.ProductID " _
> & "AND pageData LIKE '%"&session("SearchCriteria")&"%'"

Please, when showing sql statements, show us the sql statement, not the
vbscript code that is supposed to result in a variable containing a sql
statement (use response.write to print the variable's contents to the
browser window).

You really should get into the habit of using ANSI-style joins (as well as
table aliases). Here is an example that assumes related records for meta and
pageInfo will always contain the same productID:

Select i.productID, i.pageID, i.pageTitle, i.pageData,
m.metaTitle, m.Desc, p.listName
FROM pageInfo i
inner join meta m on i.pageID = m.pageID
and m.productID = i.productID
inner join products p on p.productID = i.productID
WHERE pageData LIKE '%green%'

Here is an example where the productID's could be different in the related
records:

Select i.productID pageInfoProductID,
pi.listName pageInfoProductListName,
i.pageID, i.pageTitle, i.pageData,
m.metaTitle, m.Desc, m.productID metaProductID,
pm.listName metaProductListName
FROM pageInfo i
inner join products pi on pi.productID = i.productID
inner join meta m on i.pageID = m.pageID
inner join products pm on pm.productID = m.productID
WHERE pageData LIKE '%green%'

>
> I am not sure how to display my results in a tabular form. I think
> that I realized that I cannot join these record sets casue they do not
> share the same data...
>
>
> 'here are the results say I search for 'green'
>
> productID pageTitle pageData
> ============================================================ ====
> product1 Home this is a nice green widget
> product2 How it Works can be used with a green widget
>
> 'and now for the systems table results
>
> systemID systemName systemInfo
> ============================================================ ===============
>
> system1 Cool Green widgets things to do with green
> widgets
>
>
>
>
You still haven't told me what database you are using (it is relevant - it
looks like sql server, but it might not be ...), but I think what you are
after is a union query:

select "Product" Source, productID, pageTitle, pageData
from pageInfo
WHERE pageData LIKE '%green%'
union all
select "System" Source, 0,systemID, systemInfo
from systems
where systemInfo LIKE '%green%'

When you process this recordset (open a client-side, disconnected cursor and
use the Filter property to control which source records to make available),
be aware that the source data fro each field depends on the data contained
in the Source field.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: multiple db query - join recordsets?

sorry for not stating it, but I am using an access db. I promise that
I will begin to adapt more of the formating you have demonstrated.

I'm getting an
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression ''.
/lanedb/search/results.asp, line 36

error when I try to open this recordset

Here is my select statement...

sql = "SELECT i.productID, i.pageID, i.pageTitle, i.pageData,
i.pageTitle," _
& "m.metaTitle, m.Desc, p.listName " _
& "FROM pageInfo [i] " _
& "INNER JOIN meta [m] ON i.pageID=m.pageID " _
& "AND m.productID=i.productID " _
& "INNER JOIN products [p] ON p.productID=i.productID " _
& "WHERE pageData LIKE '%"&session("SearchCriteria")&"%'"

rs.Open sql, MyConn, 1, 1 'line 36

Hmmm, what is the 'missing operator'? Do I need to have paren's around
the ON statements like
http://authors.aspalliance.com/aspxtreme/ado/structuredquery languagebasics.aspx?pageno=5

Almost there, thanks.

Re: multiple db query - join recordsets?

joe wrote:
> sorry for not stating it, but I am using an access db. I promise that
> I will begin to adapt more of the formating you have demonstrated.
>
> I'm getting an
> Microsoft JET Database Engine (0x80040E14)
> Syntax error (missing operator) in query expression ''.
> /lanedb/search/results.asp, line 36
>
> error when I try to open this recordset
>
> Here is my select statement...
>
> sql = "SELECT i.productID, i.pageID, i.pageTitle, i.pageData,
> i.pageTitle," _
> & "m.metaTitle, m.Desc, p.listName " _
> & "FROM pageInfo [i] " _
> & "INNER JOIN meta [m] ON i.pageID=m.pageID " _
> & "AND m.productID=i.productID " _
> & "INNER JOIN products [p] ON p.productID=i.productID " _
> & "WHERE pageData LIKE '%"&session("SearchCriteria")&"%'"
>
> rs.Open sql, MyConn, 1, 1 'line 36
>
> Hmmm, what is the 'missing operator'?

I'm not sure. Showing the result of
Response.write sql

would go a long way toward helping us see this problem.

> Do I need to have paren's
> around the ON statements like
>
http://authors.aspalliance.com/aspxtreme/ado/structuredquery languagebasics.aspx?pageno=5

Oh yes, of course! This is Access! You do need to group the joins using
parentheses. The best way to do this is to response.write your statement,
open your database in Access, create a new query in Design view, switch to
SQL Vies and paste the statement in from the browser window. Then switch to
Design View to verify that the join lines are drawn between the tables in
the GUI, and back toSQL View to see where Access puts the parens. To test
this query in Access, you need to replace the %'s with *'s.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: multiple db query - join recordsets?

Here is what I got from access SQL view...

sql = "SELECT pageInfo.pageID AS pageInfo_pageID, pageInfo.productID AS
pageInfo_productID,products.listName," _
& " pageInfo.pageTitle, pageInfo.pageData, meta.productID AS
meta_productID, meta.pageID AS meta_pageID," _
& " meta.metaTitle, meta.metaDesc " _
& "FROM pageInfo " _
& "INNER JOIN (meta INNER JOIN products ON meta.productID =
products.productID) " _
& " ON pageInfo.productID = products.productID " _
& "WHERE (((pageInfo.pageData) Like '%green%'))"

I just need to look at this and compare/tweak it so that I understand
and get your code to work. Now for the systems table (UNION part).

>From I've been reading, don't the total number of columns queried need
to be the same? Is there a way I could performa union of the above
select statement with this...

sql = "SELECT systemID,systemName,systemInfo,metaTitle,metaDesc FROM
systems WHERE systemInfo LIKE '%green%'"

If I can get these to work as one, then I think I'll be ready to get
things working.
Thanks

Re: multiple db query - join recordsets?

joe wrote:
> Here is what I got from access SQL view...
>
> sql = "SELECT pageInfo.pageID AS pageInfo_pageID, pageInfo.productID
> AS pageInfo_productID,products.listName," _
> & " pageInfo.pageTitle, pageInfo.pageData, meta.productID AS
> meta_productID, meta.pageID AS meta_pageID," _
> & " meta.metaTitle, meta.metaDesc " _
> & "FROM pageInfo " _
> & "INNER JOIN (meta INNER JOIN products ON meta.productID =
> products.productID) " _
> & " ON pageInfo.productID = products.productID " _
> & "WHERE (((pageInfo.pageData) Like '%green%'))"
>
> I just need to look at this and compare/tweak it so that I understand
> and get your code to work. Now for the systems table (UNION part).
>
>> From I've been reading, don't the total number of columns queried
>> need
> to be the same?

Yes. Also, you cannot mix datatypes in the same column

> Is there a way I could performa union of the above
> select statement with this...
>
> sql = "SELECT systemID,systemName,systemInfo,metaTitle,metaDesc FROM
> systems WHERE systemInfo LIKE '%green%'"
>

Let's concentrate on the SELECT clauses:

SELECT 'Page' As Source,
i.pageID AS pageInfo_pageID,
i.productID AS pageInfo_productID,
p.listName,
i.pageTitle,
i.pageData,
m.productID AS meta_productID,
m.pageID AS meta_pageID,
m.metaTitle,
m.metaDesc
FROM ...
UNION ALL
SELECT 'System',
systemID AS pageInfo_pageID,
0 AS pageInfo_productID,
systemName As listName,
systemInfo As pageTitle,
'' As pageData,
0 AS meta_productID,
0 AS meta_pageID,
metaTitle As metaTitle,
metaDesc
FROM ...

Note that the field names are controlled by the field names in the first
SELECT (I included column aliases in the second SELECT only to keep track of
what I was doing). When you process this data in the client, you need to use
the Source field to determine the contents of the other fields.

The advantage of this is performance: a single call to the database as
opposed to two. The disadvantage is the difficulty of processing the data.
If it proves to be unmanageable, then revert to using two recordsets.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.