List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:September 21 2010 5:34pm
Subject:Re: SELECT WHERE IN help
View as plain text  
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.bruehe@stripped
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

Thread
SELECT WHERE IN helpTompkins Neil21 Sep
  • Re: SELECT WHERE IN helpJohan De Meersman21 Sep
    • Re: SELECT WHERE IN helpTompkins Neil21 Sep
      • Re: SELECT WHERE IN helpJohan De Meersman21 Sep
        • Re: SELECT WHERE IN helpTompkins Neil21 Sep
          • RE: SELECT WHERE IN helpJerry Schwartz21 Sep
      • Re: SELECT WHERE IN helpMark Goodge21 Sep
  • Re: SELECT WHERE IN helpJoerg Bruehe21 Sep