MySQL SELECT ?

[Jack]

One of my database tables fields sometimes contains two items. They are
input into the field as "item1, item2" (no quotes). The query I'm using
doesn't seem to pick up any of these multiple items. I'm using

SELECT * FROM table WHERE category = 'item2'

This works fine when item2 is the only thing in the field but not when
it's shared with another value. How can I make the query find all the
instances of item2 in the field even when there are more than one value
in some of the entries?

[Jerry Gitomer]

Jack wrote:

One of my database tables fields sometimes contains two items. They are
input into the field as "item1, item2" (no quotes). The query I'm using
doesn't seem to pick up any of these multiple items. I'm using

SELECT * FROM table WHERE category = 'item2'

This works fine when item2 is the only thing in the field but not when
it's shared with another value. How can I make the query find all the
instances of item2 in the field even when there are more than one value
in some of the entries?

MySql supports the LIKE option. So,

SELECT * FROM table WHERE category LIKE '%item2%';

should work. It will be slow since it can't use any indexes to
speed up locating the rows to be retrieved.

HTH
Jerry

[Andy Hassall]

On Sat, 13 Mar 2004 15:34:28 GMT, Jack <no*****@please.com> wrote:

One of my database tables fields sometimes contains two items. They are
input into the field as "item1, item2" (no quotes). The query I'm using
doesn't seem to pick up any of these multiple items. I'm using

SELECT * FROM table WHERE category = 'item2'

This works fine when item2 is the only thing in the field but not when
it's shared with another value. How can I make the query find all the
instances of item2 in the field even when there are more than one value
in some of the entries?

Preferably by fixing your database layout. One field should contain one value.

i.e.

Item
----
item_id
category

Containing:

item_id | category
--------+--------------
1 item1, item2

... is wrong. It should be something like:

Item
----
item_id

Containing:

item_id
-------
1

... and a second table:

ItemCategory
------------
item_id

Containing:

item_id | category
--------+--------------
1 item1
1 item2

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

[Geoff Berrow]

I noticed that Message-ID: <40**********************@news.rcn.com> from
Jerry Gitomer contained the following:

SELECT * FROM table WHERE category LIKE '%item2%';

should work. It will be slow since it can't use any indexes to
speed up locating the rows to be retrieved.

For some value of slow

I'm curious why there are multiple items in a field...
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Jack]

Geoff Berrow wrote:

I noticed that Message-ID: <40**********************@news.rcn.com> from
Jerry Gitomer contained the following:

SELECT * FROM table WHERE category LIKE '%item2%';

should work. It will be slow since it can't use any indexes to
speed up locating the rows to be retrieved.

For some value of slow

I'm curious why there are multiple items in a field...

Table Fields: category, text (and several others)

There are about 25 categories and on occasion, the text fits into more
than one category.

Thank you all for your assistance.