On 4 May 2007 at 1:21, Daevid Vincent wrote:

> I'm having trouble figuring out the logic/query I want.
> I know that all those "OR"s are not right.
[snip]
> WHERE products.enabled = 1
> AND(
> (products.model LIKE 'sony%' OR products.model LIKE '20%' OR products.model LIKE 'tv%')
> OR (products.upc LIKE 'sony' OR products.upc LIKE '20' OR products.upc LIKE 'tv')
> OR (products.name LIKE '%sony%' OR products.name LIKE '20%' OR products.name LIKE '%tv%')
> OR (companies.name LIKE 'sony%' OR companies.name LIKE '20%' OR companies.name LIKE 'tv%')
> OR (categories.name LIKE '%sony%' OR categories.name LIKE '20%' OR categories.name LIKE '%tv%')
> )
[snip]

It seems to me that the logic you're looking for is something more like

(products.model LIKE 'sony%' OR products.upc LIKE 'sony' OR
products.name LIKE '%sony%' OR companies.name LIKE 'sony%' OR
categories.name LIKE '%sony%'
) AND (
products.model LIKE '20%' OR products.upc LIKE '20' OR
products.name LIKE '20%' OR companies.name LIKE '20%' OR
categories.name LIKE '20%'
) AND (
products.model LIKE 'tv%' OR products.upc LIKE 'tv' OR
products.name LIKE '%tv%' OR companies.name LIKE 'tv%' OR
categories.name LIKE '%tv%'
)

so that each of the search terms appears in at least one of the relevant
columns.
--
Iain Alexander ia@stryx.demon.co.uk