How to limit QUERY

zzz

I have 3 tables with data, lets say:

cars
trucks
boats

Each table has the following columns:

id
name
description

I also have another index_table where I store IDs of each item used earlier.

Now I need to run a search query by name but only show results if the item is already indexed in index_table

"SELECT * FROM (*3 tables*) WHERE id (*is already indexed) match like LIKE '%".$_POST['name']."%'"

I know, it's sort of convoluted but I need help to untangle this nut.

thorpe

dont you think this would be better posted in the db forum? i meen, i never visit the db forum because im not much chop with sql (and thats why i cant help you). But i would assume people who are good with sql and databases would check out the db forum and therefore be more able to answer your database question.

:-P

bradgrafelman

I agree with thorpe; perhaps a mod can move this thread if you ask nicely 😉

Anyway, try using the EXISTS keyword:

"SELECT * FROM (*3 tables*) WHERE id (*is already indexed) match like LIKE '%".$_POST['name']."%' AND EXISTS (SELECT * FROM index_table WHERE index_table.id = cars.id OR index_table.id = trucks.id OR index_table.id = boats.id)"

I'm not quite sure on this subquery:

SELECT * FROM index_table WHERE index_table.id = cars.id OR index_table.id = trucks.id OR index_table.id = boats.id

so you might want to debug that/check with someone else. What you're doing is basically trying to select rows in index_table where the id matches. I don't know exactly how you're searching, so i just tried to compare an id in index_table with an id in any of the 3 tables you mentioned.

Hope this helps, as I'm no SQL guru!

EDIT: Forgot to post a link to help you out too; here's MySQL's manual page on EXISTS and NOT EXISTS!

zzz

I agree I should've posted on db forum this question, however your pointer to EXISTS appears to be exactly what I need.

I also noticed that db forum is not as active and often my messages hang it there for ever before I get any suggestions if ever. Newbies forum has proven once and again a kind of resources that one gets addicted to after getting nearly instant insightful clues from the talent pool from around the globe (despite the annoying irrelevant ads plastered throughout).

bradgrafelman

So did that solve your problem? Just wanted to make sure; if it did, don't forget to MARK THIS THREAD RESOLVED using the link at the bottom of the page.

thorpe

heck... ive been waiting like 3-4 days for a decent response to my thread in the linux help forum.

patience is a vertue.

SumitGupta

Hi,

The sub query only run on Mysql version 4.1.* . so please check the same with your Mysql version (if you are using one).

zzz

Shoot!

I called and apparently my site's MySQL Version 4.0.15 and
PHP Version 4.3.7

So, what are my options?

bradgrafelman

Demand an upgrade or you'll take your money elsewhere :p