Re: Problem using IN statement MySQL 5
Paul Nowosielski <[EMAIL PROTECTED]> 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
Re: Problem using IN statement MySQL 5
Paul, SELECT * from encore enc, article art WHERE enc.encore_id= '10' AND `article_id` IN (`articles`) First, IN() accepts either a literal value list, or a subquery which returns a value list. Just passing it a table name won't work. Second, your join looks suspicious. Is this what you mean?... SELECT * FROM encore AS enc INNER JOIN article AS art USING (article_id) WHERE enc.encore_id= 10; PB - Paul Nowosielski wrote: 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! -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.17/226 - Release Date: 1/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem using IN statement MySQL 5
Your select has two tables, but you don't join them. Can you provide the table info, and a subset of the data that should be pulled back by this query? David Paul Nowosielski wrote: 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! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem using IN statement MySQL 5
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
