ASP & Access 'LIKE' queries 3

[chappi]

I stumbled across an interesting problem. I need to query multiple access tables using a 'LIKE' query (kinda like a search engine for a website)...

Whenever I use more than one table or more than one 'LIKE' my ASP page times out.

Here is my original code:

mySQL = "SELECT * FROM items, types, mediums, categories WHERE first_name LIKE '%" & mySearch & "%' OR last_name LIKE '%" & mySearch & "%' AND categories.id=categorie AND mediums.id=items.medium AND types.id=items.type"

The variable mySearch represents the value that a user would type into the search screen on the previous page.

I've tested out a bunch of scenarios:
1. Has no problems getting the results if there is only 1 record returned
2. Has no problems if no records are returned
3. Using only 1 table in the query works fine
4. OR using only 1 LIKE statement works fine

If anyone has ever come across this same problem and/or has a solution, I would really appreciate a hint...it's been driving me nutty

Thanks!
Jeannette

 

[guitardave78]

Could you not try this kind of thing, putting your search tables into one

mySQL = "SELECT * FROM items, types, mediums, categories, last_name &" "& first_name as name WHERE name LIKE '%" & mySearch & "%' AND categories.id=categorie AND mediums.id=items.medium AND types.id=items.type"

 

[DreXor]

just looks like you need to break your logic into seperate parts...

mySQL = "SELECT * FROM items, types, mediums, categories WHERE [red](first_name LIKE '%" & mySearch & "%' OR last_name LIKE '%" & mySearch & "%')[/red] AND categories.id=categorie AND mediums.id=items.medium AND types.id=items.type"

just added some parens to allow the AND's and OR's to work seperately instead of against each other

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[DreXor]

ahhh ... and i overlooked that, you're making the db work extra hard with all those tables and that *

pull what you need and use the tablenames.fieldnames to help it.

also might be more agreeable if you used a join in there instead of all the table/field combos

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[chappi]

DreX -- You are a life saver!

Thanks!

 

[DreXor]

star for dave too for catching that

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[guitardave78]

lol thanks