[RESOLVED] stuck on search query..

[cretaceous]

got a query doing a search function..
it's searching a setup of categorised text entries with numerous sub categories

$querystring="SELECT * FROM rvus, cat,  tbls WHERE (hdng LIKE '$srchstring%' 
OR txt LIKE '%$srchstring%') AND rvus.cat_id=cat.cat_id  

AND ((tbls.scat_id=rvus.scat_id AND rvus.scat_id!=0) 
OR (rvus.scat_id =0 AND cat.cat_id = cat.cat_id)) ORDER BY cat_name, hdng, dat ";

this bit is wrong and I'm not sure what to do with it:

..AND ((tbls.scat_id=rvus.scat_id AND rvus.scat_id!=0) OR (rvus.scat_id =0 AND cat.cat_id = cat.cat_id))...

basically its possible for a text entry (rvus table) to be entered against a top level category (cat table) ... and if so it wont have an entry in the sub-category table (tbls)

so what I'm trying to do is also get results for: OR rvus.scat_id=0 AND "dont match anything but return only the result"

this works:

"SELECT * FROM rvus, cat WHERE (hdng LIKE '$srchstring%' OR txt LIKE '%$srchstring%' ) 
AND rvus.cat_id=cat.cat_id  ORDER BY cat_name, hdng, dat "

but doesnt cross reference to the subcategory (tbls) table
thanks!

[cretaceous]

no replies - maybe if I condense the details a bit someone can help:

QUERY A:
$querystring="SELECT * FROM rvus, cat 
WHERE (hdng LIKE '$srchstring%' OR txt LIKE '%$srchstring%' ) 
AND rvus.cat_id=cat.cat_id  

ORDER BY cat_name ";

QUERY B:
$querystring="SELECT * FROM rvus, cat, tbls
WHERE (hdng LIKE '%$srchstring%' OR txt LIKE '%$srchstring%')
AND rvus.cat_id = cat.cat_id
AND ((tbls.scat_id=rvus.scat_id AND rvus.scat_id!=0))
ORDER BY cat_name ";

both work, but A doesnt get the info from the 'tbls' table
while B gets the cross links but fails to return the non cross linked entries

I understand why A and B behave as they do
I dont know how to combine the two -
what I want is to get distinct results where rvus.scat_id==0

calling mr happiness: you answered a similar problem a while ago!
some people seem to get mysql queries just like that.. not me

[cretaceous]

LEFT JOIN dumbo