MySQL Query

[thesimplerules@gmail.com]

I want to have a field where I store a list of what categories that
particular row falls under, I don't know which format to do or how I
would query it.

For example, row1 might belong to categories 1, 3 & 7 - I'd want to be
able to query all rows belonging to 3 and get row1.

[Alvaro G. Vicario]

Re: MySQL Query

*** thesimplerules@ gmail.com escribió/wrote (26 Aug 2006 10:07:08 -0700):

I want to have a field where I store a list of what categories that
particular row falls under, I don't know which format to do or how I
would query it.

The logical approach in a normalized database would be to create a separate
table with the following fields:

foo_to_categori es
=============== ==

id_foo - Foreign key
id_category - Foreign key

For example, row1 might belong to categories 1, 3 & 7

Stored data would like like this:

1 1
1 3
1 7

I'd want to be able to query all rows belonging to 3 and get row1.

SELECT *
FROM categories
INNER JOIN foo_to_categori es
ON categories.id_c ategory=foo_to_ categories.id_c ategory
WHERE foo_to_categori es.id_category= 3



Replace "foo" with the actual object name, I figured out it won't be "row"
unless you're building a ticket selling system ;-P


P.S. Code is untested

--
-+ http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
++ Mi sitio sobre programación web: http://bits.demogracia.com
+- Mi web de humor con rayos UVA: http://www.demogracia.com
--

[Jerry Stuckle]

Re: MySQL Query

thesimplerules@ gmail.com wrote:

I want to have a field where I store a list of what categories that
particular row falls under, I don't know which format to do or how I
would query it.
>
For example, row1 might belong to categories 1, 3 & 7 - I'd want to be
able to query all rows belonging to 3 and get row1.
>

Answered in comp.database.m ysql.

And if you're going to post to several newsgroups, please:

- Pick appropriate newsgroups. c.l.p isn't one of them for a SQL question
- Cross-post your question instead of multi-posting.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===