brings the same result as
select
*
from
fruit,
fruit_animal,
animal
where
fruit.id = fruit_animal.id_fruit
AND
fruit_animal.id_animal = animal.id
order by rand()
or i got something wrong
the next thing is that the tables are hughe, like 3 millionen rows (growing)
thanks btw :O
<[EMAIL PROTECTED]> schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
> OK, This is a similar solution to a problem posted last month (he was
> trying to match debits to credits). Here's how it works:
>
> Create a temporary table to match your fruits_animals table except you
> want to put UNIQUE INDEXES on both columns individually. Then you run an
> INSERT IGNORE to copy the rows from fruits_animals into your temp table.
> What you will have when the INSERT IGNORE completes is a list that
> contains all of your animals listed only once and all of the fruits listed
> only once but only if that animal/fruit combination already existed.
>
> CREATE TEMPORARY TABLE tmpDedupe(
> animal_id int not null,
> fruit_id int not null,
> UNIQUE INDEX (animal_id),
> UNIQUE INDEX (fruit_id)
> )
>
> INSERT IGNORE tmpDedupe (animal_id, fruit_id)
> SELECT (id_fruits, id_animals)
> FROM fruits_animals
>
> SELECT *
> FROM tmpDedupe
> ORDER BY RAND()
> LIMIT 100
>
> DROP TABLE tmpDedupe
>
> Make sense?
>
> Yours,
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
> "Gerske, Sebastian" <[EMAIL PROTECTED]> wrote on 07/20/2004 09:22:30 AM:
>
> > well neither a,b or c :P
> >
> > 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
> >
> >
> > <[EMAIL PROTECTED]> schrieb im Newsbeitrag
> >
>
news:[EMAIL PROTECTED]
> > > Sebastian,
> > >
> > > I don't think we completely understand your question....or someone
> would
> > > have responded long before now. Let me see if I can rephrase the
> > > situation and maybe we can get a response.
> > >
> > > You have two tables that contain objects (your example: fruits and
> > > animals) and a table that relates them (example: fruits_animals) in a
> > > many-to-many relationship.
> > >
> > > Is your situation:
> > > A) You want to return 100 un-duplicated random combinations of the
> > > objects, regardless of if they have an entry in the relationship
> table?
> > > - OR -
> > > B) You want a list of 100 un-duplicated random rows from the
> relationship
> > > table?
> > > - OR -
> > > C) Each time you run your query containing "ORDER BY RAND()" , you get
> the
> > > exact same set of records back?
> > >
> > > Yours,
> > > Shawn Green
> > > Database Administrator
> > > Unimin Corporation - Spruce Pine
> > >
> > >
> > > "Gerske, Sebastian" <[EMAIL PROTECTED]> wrote on 07/20/2004 06:18:11
> AM:
> > >
> > > > help me please :/
> > > >
> > > >
> > > > ""Gerske, Sebastian"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
> > > > news:[EMAIL PROTECTED]
> > > > > Hello list,
> > > > >
> > > > > i still need a solution for my 3 table join rand problem, im using
> > > version
> > > > > 4.1 now so subqueries are possible:
> > > > >
> > > > > explanation:
> > > > >
> > > > > i have 3 tables lets say:
> > > > >
> > > > > fruits:
> > > > >
> > > > > id, name
> > > > > 1 banana
> > > > > 2 apple
> > > > > 3 strawberry
> > > > >
> > > > > fruits_animals
> > > > >
> > > > > id, id_fruits, id_animals
> > > > > 1 1 2
> > > > > 2 1 3
> > > > > 3 3 1
> > > > > 4 2 4
> > > > >
> > > > > animals
> > > > >
> > > > > id, name
> > > > > 1 cat
> > > > > 2 dog
> > > > > 3 mouse
> > > > > 4 elephant
> > > > >
> > > > >
> > > > > My problem is that if i join the tables and order them by rand i
> > > always
> > > > get
> > > > > one result something like:
> > > > > strawberry, cat (fruit id = 3, animal id = 1 )
> > > > > or
> > > > > banana, dog (fruit id = 1, animal id = 2)
> > > > > but never
> > > > > banana, mouse( fruit id = 1, animal id = 3 )
> > > > >
> > > > > and need to select 100 different relations without redundancies
> hows
> > > that
> > > > > possible ?
> > > > >
> > > > > regards sebastian gerske
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > 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]
> >
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
