Oops, this was not correct, excuse me !
You can have duplicate id_animals with this query.
You can do it like this :
SELECT my_id_fruits, my_id_animals FROM
(SELECT id_fruits AS my_id_fruits,
(SELECT id_animals
FROM fruits_animals
WHERE id_fruits = my_id_fruits
ORDER BY RAND()
LIMIT 1) AS my_id_animals
FROM fruits_animals
GROUP BY my_id_fruits) AS tmpQuery
GROUP BY tmpQuery.my_id_animals
ORDER BY whatever you want
LIMIT 100;
On 20 Jul 2004 at 16:36, Arnaud <[EMAIL PROTECTED]> wrote:
> > i have lets say 1000 different fruits
> > and 1000 different animals
> > with many to man relations
> > now i want to extract 100 differnt fruits held by 100 different
> > animals without dupes of fruit or animal
>
> That's a nice one ! I'll give it a try :
> The point is to get 100 random couples of (id_fruits, id_animals),
> with unique id_fruits and unique id_animals, right ?
>
> SELECT id_fruits AS my_id_fruits,
> (SELECT id_animals
> FROM fruits_animals
> WHERE id_fruits = my_id_fruits
> ORDER BY RAND()
> LIMIT 1) AS my_id_animals
> FROM fruits_animals
> GROUP BY my_id_fruits
> ORDER BY RAND()
> LIMIT 100;
>
> You have your unique many-to-many relations' table, you just have to
> join this with the animals and fruits tables.
>
> Regards,
> Arnaud
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
