From: Joerg Bruehe
Date: September 21 2010 5:34pm
Subject: Re: SELECT WHERE IN help
List-Archive: http://lists.mysql.com/mysql/223082
Message-Id: <4C98ECB8.4030707@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

Hi Neil, all!


Tompkins Neil wrote:
> Hi
>=20
> 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 ("+=3D" means appending t=
o
a string):

  value =3D first value of the list;
  statement =3D "SELECT * FROM my_table WHERE record_id =3D $value";

  while (there are more values in the list)
  do
      value =3D next value of the list;
      statement +=3D
          "UNION SELECT * FROM my_table WHERE record_id =3D $value";
  done;

  statement +=3D ";";

  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=F6rg

--=20
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