Paul Nowosielski <paul@stripped> wrote on 01/11/2006 02:41:05
PM:
> Hello,
>
> I'm trying to run q query with an IN statement in MySQL 5. Like so:
>
> SELECT * from encore enc, article art
> WHERE enc.encore_id= '10' AND `article_id` IN (`articles`)
>
> Its should return all the articles in the encore.articles column but
> instead only returns the first article.
>
> In encore,articles is the data 43,44,45,46.
> These are article IDs. If I manually place 43,44,45,46 into the query
> like so:
>
> SELECT * from encore enc, article art
> WHERE enc.encore_id= '10' AND `article_id` IN (43,44,45,46)
>
> All 4 articles are returned. Any ideas why this is not working?
>
> TIA!
>
> --
> Paul Nowosielski
> Webmaster
> 2401 Broadway St
> Boulder, Co 80304
> Tel: 303.440.0666 ext:219
> Cell: 303.827.4257
> www.celebrityaccess.com
> www.protouronline.com
> www.boxofficenetwork.com
>
They look similar but an actual list of numeric values is not the same as
a string containing a list of numeric values. Your `articles` column in
your `encore` table contains the single string "43,44,45,46" which
converts to a single numeric value of 43. That's why you only see the one
row returned.
What you want to be able to do is do treat that string as separate values.
One way to do this with MySQL is with the function FIND_IN_SET() but that
will negate the use of indexes. Check out the other SET and string related
functions as parts of alternate solutions.
http://dev.mysql.com/doc/refman/5.0/en/set.html
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
The better solution is to re-organize your data so that there is a third
table that matches up encores to articles. This two-column table could
contain millions of entries and you might think this will slow things down
but the engine will be able to use indexes and your queries will actually
move MUCH faster. Even on a few hundred entries you will be able to notice
that FIND_IN_SET() will be "slow".
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
