List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 11 2006 8:07pm
Subject:Re: Problem using IN statement MySQL 5
View as plain text  
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



Thread
Problem using IN statement MySQL 5Paul Nowosielski11 Jan
  • Re: Problem using IN statement MySQL 5David Griffiths11 Jan
  • Re: Problem using IN statement MySQL 5Peter Brawley11 Jan
  • Re: Problem using IN statement MySQL 5SGreen11 Jan