Hi Neil, all!
Tompkins Neil wrote:
> 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
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,
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
value = first value of the list;
statement = "SELECT * FROM my_table WHERE record_id = $value";
while (there are more values in the list)
value = next value of the list;
"UNION SELECT * FROM my_table WHERE record_id = $value";
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
Before going that route, you should question your assumptions: Why is it
necessary to return the same record twice?
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