Charlie Schaubmair wrote:
> Hello,
>
> I want to do a query where I only gt the results by a numeric value:
>
> select * from MyTable where 1 IN someFieldInMyTable
> I know this query doesn't work, but maybe anyone knows what I mean.
>
> 1 can be a value betwenn 1 and 23
> someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21
Mike van Hoof wrote:
> try:
>
> SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
> This will also give you results like 21
>
> What I usally do in these cases is build the values like this:
>
> [1][2][3][21]
>
> And then the query:
>
> SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'
>
> Mike
Charlie Schaubmair wrote:
> Hello Mike,
>
> thx, but isn't there another way?
Mike van Hoof wrote:
> maybe there is, but i don't know why... because when you do a like query
> it also finds the 21 when you do a like on the 1.
> what you maybe can do (not tested) is:
>
> SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable
> WHERE wherefield='%,1,%'
>
> Now your someFieldInMyTable is CONCATed to ,1,2,3,4,5,6,9,21, and over
> that value the where clause is done...
Mike van Hoof wrote:
> Sorry, query was wrong... has to be:
>
> SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable
> WHERE wherefield LIKE '%,1,%'
You've just discovered why this is not the way to store a list of attributes.
Stuffing multiple values in a single cell is a bad idea. The correct way to
implement this is to store the attributes in a separate table, one per row.
That is, instead of
MyTable
=======
MyTable_id other columns attributes
1 ... 1,2,3,4,5,6,9,21
2 ... 5,7,13
you would do this:
MyTable_attributes
=======
MyTable_id other columns
1 ...
2 ...
MyTable_attributes
==================
MyTable_id attribute
1 1
1 2
1 3
1 4
1 5
1 6
1 9
1 21
2 5
2 7
2 13
Then finding rows in MyTable which have attribute "1" becomes trivial:
SELECT m.*
FROM MyTable m
JOIN MyTable_attributes ma ON m.MyTable_id = ma.MyTable_id
WHERE ma.attribute = 1;
With a UNIQUE index on (attribute, MyTable_id) in MyTable_attributes, this will
be very quick.
====
That said, you can find what you want with your current schema using the
FIND_IN_SET() function
<http://dev.mysql.com/doc/refman/4.1/en/string-functions.html>.
SELECT *
FROM MyTable_attributes
WHERE FIND_IN_SET(1, someFieldInMyTable);
This works so long as the values in someFieldInMyTable are separated by commas.
If you switch to some other separator, such as enclosing attributes in
brackets, it won't work.
Note that no index on someFieldInMyTable can be used for this query, however, so
it requires a full-table scan.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
