Re: join subquerie rand problem

2004-07-21 Thread Gerske, Sebastian 
thats exactly what i wanted - the perfekt solution, now i have to thing
about performance

but thanks to Shawn Green for trying to help :

Arnaud [EMAIL PROTECTED] schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
  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]

Re: join subquerie rand problem

2004-07-20 Thread Gerske, Sebastian 
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
 1banana
 2apple
 3strawberry

 fruits_animals

 id,   id_fruits,   id_animals
 11   2
 21   3
 33   1
 42   4

 animals

 id,   name
 1cat
 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]

Re: join subquerie rand problem

2004-07-20 Thread Gerske, Sebastian 
i think its not even possible with subqueries



Gerske, Sebastian [EMAIL PROTECTED] schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
 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
  1banana
  2apple
  3strawberry
 
  fruits_animals
 
  id,   id_fruits,   id_animals
  11   2
  21   3
  33   1
  42   4
 
  animals
 
  id,   name
  1cat
  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]

Re: join subquerie rand problem

2004-07-20 Thread SGreen 
Sebastian,

I don't think we completely understand your questionor 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
  1banana
  2apple
  3strawberry
 
  fruits_animals
 
  id,   id_fruits,   id_animals
  11   2
  21   3
  33   1
  42   4
 
  animals
 
  id,   name
  1cat
  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]

Re: join subquerie rand problem

2004-07-20 Thread Gerske, Sebastian 
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 questionor 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
   1banana
   2apple
   3strawberry
  
   fruits_animals
  
   id,   id_fruits,   id_animals
   11   2
   21   3
   33   1
   42   4
  
   animals
  
   id,   name
   1cat
   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]

Re: join subquerie rand problem

2004-07-20 Thread SGreen 
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 questionor 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
1banana
2apple
3strawberry
   
fruits_animals
   
id,   id_fruits,   id_animals
11   2
21   3
33   1
42   4
   
animals
   
id,   name
1cat
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]

Re: join subquerie rand problem

2004-07-20 Thread Gerske, Sebastian 
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 questionor 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
 1banana
 2apple
 3strawberry

 fruits_animals

 id,   id_fruits,   id_animals
 11   2
 21   3
 33   1
 42   4

 animals

 id,   name
 1cat
 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]

Re: join subquerie rand problem

2004-07-20 Thread Arnaud 
 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]

Re: join subquerie rand problem

2004-07-20 Thread Arnaud 
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]