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, [email protected]
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/[email protected]
