On 1 Oct, 23:41, validus <valid...@gmail.com> wrote:
> Let me try to clarify some more. This is a description field with a
> column type of text.
> It's purpose is to store a description about something. If we were
> talking about
> products, it could be the product description.
>
> Imagine we had a camera product we were storing. The desc might be
> something
> like:
> ================================================== ===========
> Example Description Text
>
> This new Sony camera provides 5 megapixel resolution for big prints
> and photo cropping,
> large 2.5-inch LCD screen, advanced optics and the appeal of
> traditional
> rangefinder shooting--so photo enthusiasts will have a camera thats
> equally
> adept at saving their memories and capturing their vision. It also
> features a
> 3x optical zoom.
> ================================================== ===========
> The description is descriptive text about what we are modeling.
> You can't normalize a description like this out as they
> will very from item to item at least in our application and there is
> only one
> description per item. I understand that many product types have
> specification
> lists and that can be normalized out to other tables and displayed
> along with
> description but that is NOT what we are talking about here.
>
> When I said "terms" maybe I used the wrong word. Perhaps keywords
> would
> have been better. Using the above example, say the user wanted to
> show
> products where the description contained the keywords Sony, optical,
> and new
> but we wouldn't want to consider order as the user doesn't care what
> order
> they appear in but wants to see only the products that mention all 3
> of these
> things in the description.
>
> Thanks for the input.
>
> validus
> - Show quoted text -

Would have saved a lot of time if you'd explained this or used more
deswcriptive examples at the start!

In the absence of FULLTEXT, how about:

SELECT
id,
desc
FROM db
WHERE desc LIKE '%red%' AND desc LIKE '%white%' AND desc LIKE '%blue%'