Re: AW: find in list
Hey Charlie,
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...
Maybe it helps
Mike
Charlie Schaubmair schreef:
Hello Mike,
thx, but isn't there another way?
br
Charlie
-Ursprüngliche Nachricht-
Von: Mike van Hoof [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 16. August 2006 10:46
An: Charlie Schaubmair
Cc: mysql@lists.mysql.com
Betreff: Re: find in list
Charlie Schaubmair schreef:
Hello,
I want to do a query where I only gt the results by an
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
br
Charlie
Hey
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 te query:
SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'
Mike
--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
Uw bedrijf voor Multimedia op Maat
--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
Uw bedrijf voor Multimedia op Maat
Re: AW: find in list
Sorry, query was wrong... has to be:
SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable
WHERE wherefield LIKE '%,1,%'
Mike van Hoof schreef:
Hey Charlie,
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...
Maybe it helps
Mike
Charlie Schaubmair schreef:
Hello Mike,
thx, but isn't there another way?
br
Charlie
-Ursprüngliche Nachricht-
Von: Mike van Hoof [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 16.
August 2006 10:46
An: Charlie Schaubmair
Cc: mysql@lists.mysql.com
Betreff: Re: find in list
Charlie Schaubmair schreef:
Hello,
I want to do a query where I only gt the results by an
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
br
Charlie
Hey
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 te query:
SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'
Mike
--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
Uw bedrijf voor Multimedia op Maat
--
Medusa, Media Usage Advice B.V.
Science Park Eindhoven 5216
5692 EG SON
tel: 040-24 57 024
fax: 040-29 63 567
url: www.medusa.nl
mail: [EMAIL PROTECTED]
Uw bedrijf voor Multimedia op Maat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: find in list
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]
