Re: SELECT WHERE IN help
If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT WHERE IN help
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT WHERE IN help
I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT WHERE IN help
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a table. I think in this case I need to change my design . On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote: I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SELECT WHERE IN help
On 21/09/2010 16:44, Tompkins Neil wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? No, that isn't possible. Why do you want a duplicate record to be retrieved? There may be a better way of doing it. Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT WHERE IN help
Hi Neil, all! Tompkins Neil wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? This is a case where you may safely use natural language and logic. The command is SELECT all fields FROM the records in mytable FOR WHICH THE FOLLOWING CONDITION IS TRUE: the field record_id has a value which is IN the list 3, 4, 5, 6, 7, 3 The condition can only evaluate to true or false (ignoring NULL values and the unknown truth value for now), and for that evaluation it does not matter whether a matching value appears in your list only once or repeatedly. To achieve your desired effect, you might use a generator to create a UNION statement. Roughly, the approach would be (+= means appending to a string): value = first value of the list; statement = SELECT * FROM my_table WHERE record_id = $value; while (there are more values in the list) do value = next value of the list; statement += UNION SELECT * FROM my_table WHERE record_id = $value; done; statement += ;; execute statement; Obviously, this will create a huge statement if the value list is long, and it doesn't seem to be efficient, so I don't recommend this technique in general. Before going that route, you should question your assumptions: Why is it necessary to return the same record twice? Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SELECT WHERE IN help
-Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Tuesday, September 21, 2010 11:48 AM To: Johan De Meersman Cc: [MySQL] Subject: Re: SELECT WHERE IN help Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a table. I think in this case I need to change my design . [JS] You can accomplish your goal by using a sub-select to create a table that has 3 in it twice, and then JOIN it to the original table. As for why you would want to do this, that's another story. It sounds like you went down the wrong road. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman vegiv...@tuxera.bewrote: I don't think that'll work, no. Why would you want to return duplicate data ? The whole point of an RDBMS is to *avoid* duplicate data :-) On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? Cheers Neil On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meersman vegiv...@tuxera.bewrote: If there are two, you will return two. On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I return two records for the record_id 3 ? Is it possible ? Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
