[RESOLVED] combine 2 queries but with different columns

cretaceous

I have a query for a basic search function:

$querystring="SELECT * FROM rvus, cat, loc WHERE (hdng LIKE '$srchstring%' OR txt LIKE 
'%$srchstring%' OR loc_strt LIKE '%$srchstring%' OR loc_dst LIKE '%$srchstring%' OR 
loc_tn LIKE '%$srchstring%') AND rvus.cat_id=cat.cat_id AND cat.cat_id=$cat_id AND 
loc.rvuid=rvus.revu_id  ORDER BY dat desc

now the things being searched are sometimes linked to the loc table (it lists location address) and sometime they are not, as its not always relevant

so I'd like to repeat the above query, removing all references to the loc table, and then combine the two queries as a UNION - but I cant because of different numbers of columns

(I do have a column in the 'rvus' table which specifies if there is a link to the loc table - I dont know if that is useful here or not)

any advice appreciated -
I havent done a query sort with arrays but am thinking thats probably the solution?

dnast

Ouch, it's so cryptic. 🙂

Would a LEFT JOIN for location data not work? If there's no location data for a specific item, the other information will still be included in the result leaving the location field blank.

cretaceous

didnt mean to be cryptic! - maybe this is better..

first query:

 $querystring="SELECT * FROM rvus, cat, loc WHERE (hdng LIKE '$srchstring%' OR txt LIKE 
'%$srchstring%' OR loc_strt LIKE '%$srchstring%' OR loc_dst LIKE '%$srchstring%' OR 
loc_tn LIKE '%$srchstring%') AND rvus.cat_id=cat.cat_id AND cat.cat_id=$cat_id AND 
loc.rvuid=rvus.revu_id  ORDER BY dat desc

that works where a review entry has a crosslink to the loc (location) table
but a review entry without a crosslink does not appear because of "AND
loc.rvuid=rvus.revu_id"

so an alernate query of this:

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

does the job of picking up reviews where the location entry is void and the loc table is unneeded

All I'm hoping to do is combine the two queries but I cant figure out how!

mrhappiness

WHERE
  (hdng LIKE ...)
  AND rvus.cat_id=cat.cat_id 
  AND cat.cat_id=$cat_id 
  AND (rvus.loc = 0 OR loc.rvuid=rvus.revu_id)

cretaceous

mrhappiness - you just made me happy!
here tis:

$querystring="SELECT * FROM rvus, cat, loc WHERE (hdng LIKE '$srchstring%' OR txt LIKE '%$srchstring%' OR loc_strt LIKE '%$srchstring%' OR loc_dst LIKE '%$srchstring%' OR loc_tn LIKE '%$srchstring%') AND rvus.cat_id=cat.cat_id AND ((rvus.loc!=1 AND loc.loc_id=1) OR (rvus.loc=1 AND loc.rvuid=rvus.revu_id)) ORDER BY cat_name, dat desc"

(and I inserted an empty entry in the loc table at id 1)