creating search engine

[Jimmy Tran]

Hello everyone,

I have one major table that contains some data and contain keys to many
other tables. I am using RIGHT JOIN query method to get all my data and
everything works very well. Now I am using the same query except i have
a bunch of WHERE clauses in it to create a 'search engine', here's a
fake example:
SELECT a.a AS A, b.b AS B, c.c AS C, ..., and so on
FROM a RIGHT JOIN (b RIGHT JOIN C RIGHT JOIN(...so on)
ON a.a=c.c.......and so on
WHERE (a.a LIKE '%searchTerm%') OR (b.b LIKE '%searchTerm%')
OR (b.b LIKE '%searchTerm%')OR (c.c LIKE '%searchTerm%')
....and so on for about 25 WHERE cases total

It works fine and fast when I have about 13 WHERE clauses, but when i
exceed that, my script runs forever. I would like thank you in advance
if anyone could help me out.
Jimmy.
I use asp,vb script, and access97 for my project.
Again, thank you so much.



*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Jimmy Tran]

Hello everyone,

I have one major table that contains some data and contain keys to many
other tables. I am using RIGHT JOIN query method to get all my data and
everything works very well. Now I am using the same query except i have
a bunch of WHERE clauses in it to create a 'search engine', here's a
fake example:
SELECT a.a AS A, b.b AS B, c.c AS C, ..., and so on
FROM a RIGHT JOIN (b RIGHT JOIN C RIGHT JOIN(...so on)
ON a.a=c.c.......and so on
WHERE (a.a LIKE '%searchTerm%') OR (b.b LIKE '%searchTerm%')
OR (b.b LIKE '%searchTerm%')OR (c.c LIKE '%searchTerm%')
....and so on for about 25 WHERE cases total

It works fine and fast when I have about 13 WHERE clauses, but when i
exceed that, my script runs forever. I would like thank you in advance
if anyone could help me out.
Jimmy.
I use asp,vb script, and access97 for my project.
Again, thank you so much.

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Ray at]

[url]www.aspfaq.com/5003[/url]

Ray at work

"Jimmy Tran" <hunganh20@yahoo.com> wrote in message
news:um2Wkp5dDHA.3464@TK2MSFTNGP11.phx.gbl...

> Hello everyone,
>
> I have one major table that contains some data and contain keys to many
> other tables. I am using RIGHT JOIN query method to get all my data and
> everything works very well. Now I am using the same query except i have
> a bunch of WHERE clauses in it to create a 'search engine', here's a
> fake example:
> SELECT a.a AS A, b.b AS B, c.c AS C, ..., and so on
> FROM a RIGHT JOIN (b RIGHT JOIN C RIGHT JOIN(...so on)
> ON a.a=c.c.......and so on
> WHERE (a.a LIKE '%searchTerm%') OR (b.b LIKE '%searchTerm%')
> OR (b.b LIKE '%searchTerm%')OR (c.c LIKE '%searchTerm%')
> ...and so on for about 25 WHERE cases total
>
> It works fine and fast when I have about 13 WHERE clauses, but when i
> exceed that, my script runs forever. I would like thank you in advance
> if anyone could help me out.
> Jimmy.
> I use asp,vb script, and access97 for my project.
> Again, thank you so much.
>
>
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Ray at]

[url]www.aspfaq.com/5003[/url]

Ray at work

"Jimmy Tran" <hunganh20@yahoo.com> wrote in message
news:%23Cmb3p5dDHA.3992@TK2MSFTNGP11.phx.gbl...

> Hello everyone,
>
> I have one major table that contains some data and contain keys to many
> other tables. I am using RIGHT JOIN query method to get all my data and
> everything works very well. Now I am using the same query except i have
> a bunch of WHERE clauses in it to create a 'search engine', here's a
> fake example:
> SELECT a.a AS A, b.b AS B, c.c AS C, ..., and so on
> FROM a RIGHT JOIN (b RIGHT JOIN C RIGHT JOIN(...so on)
> ON a.a=c.c.......and so on
> WHERE (a.a LIKE '%searchTerm%') OR (b.b LIKE '%searchTerm%')
> OR (b.b LIKE '%searchTerm%')OR (c.c LIKE '%searchTerm%')
> ...and so on for about 25 WHERE cases total
>
> It works fine and fast when I have about 13 WHERE clauses, but when i
> exceed that, my script runs forever. I would like thank you in advance
> if anyone could help me out.
> Jimmy.
> I use asp,vb script, and access97 for my project.
> Again, thank you so much.
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[ljb]

Off the top of my head...this might work

WHERE (a.a & b.b & c.c LIKE '%searchTerm%')

I just had an idea to concatenate all the fields and search them once. Don't
know if it will work though.

"Jimmy Tran" <hunganh20@yahoo.com> wrote in message
news:#Cmb3p5dDHA.3992@TK2MSFTNGP11.phx.gbl...

> Hello everyone,
>
> I have one major table that contains some data and contain keys to many
> other tables. I am using RIGHT JOIN query method to get all my data and
> everything works very well. Now I am using the same query except i have
> a bunch of WHERE clauses in it to create a 'search engine', here's a
> fake example:
> SELECT a.a AS A, b.b AS B, c.c AS C, ..., and so on
> FROM a RIGHT JOIN (b RIGHT JOIN C RIGHT JOIN(...so on)
> ON a.a=c.c.......and so on
> WHERE (a.a LIKE '%searchTerm%') OR (b.b LIKE '%searchTerm%')
> OR (b.b LIKE '%searchTerm%')OR (c.c LIKE '%searchTerm%')
> ...and so on for about 25 WHERE cases total
>
> It works fine and fast when I have about 13 WHERE clauses, but when i
> exceed that, my script runs forever. I would like thank you in advance
> if anyone could help me out.
> Jimmy.
> I use asp,vb script, and access97 for my project.
> Again, thank you so much.
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Matt Smith]

Rather than having 13 clauses all tied together and conflicting with one
another over bracket placement an effective (though unsophisticated)
solution is to use a concatenated single search field.

Assuming MS SQL Server
CREATE VIEW vwSearchField AS
SELECT tablename.result field, LOWER(tablename.search field 1+
COALESCE(' ' + tablename.search field 2,'')+
COALESCE(' ' + different table.another search field,'')+ etc..
FROM tablename INNER JOIN etc..

You can then easily create 3 different search types to apply to this view.
SELECT tablename.result field FROM tablename INNER JOIN vwSearchField ON
tablename.resultfield = vwSearchField.resultfield

Any words in search:
WHERE vwSearchField.strSearchField LIKE '%keyword1%' OR
vwSearchField.strSearchField LIKE '%keyword2% OR etc..

Any words in search:
WHERE vwSearchField.strSearchField LIKE '%keyword1%' AND
vwSearchField.strSearchField LIKE '%keyword2% AND etc..

Exact phrase
Any words in search:
WHERE vwSearchField.strSearchField LIKE '%keyword1 keyword2 keyword 3%'

This method works perfectly well for me and means that by just altering your
view you can easily add new search fields.
As I say, it's effective but you often get back a lot more results than you
might want with the OR search.
HTH

Matt Smith