Re: join subquerie rand problem
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]
join subquerie rand problem
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
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
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
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
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]
Repost: Order by RAND + join problem
Hello list, im having a hughe problem with the RAND() function first of all im using mysql 3.23 so subquerys are impossible. Im having three tables which are joined by ID's now i want to select a single row random out of the join set whats the best way to do it? My Table structure is: table1 --- | id | content | | 1 | apple | --- relation_table | id | id_table1 | id_table2 | | 1 | 1 | 1 | | 2 | 1 | 2 | table2 - | id | content | | 1 | bear | | 2 | ape | - The result should be somehting like: Query1 (randomly generated): -- | table1.id | table1.content | table2.id | table2.content | | 1| apple | 1| bear | - or Query2 (randomly generated): -- | table1.id | table1.content | table2.id | table2.content | | 1| apple | 2| ape | - and so on .. thanks for comments / suggestions / solutions -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oder by RAND Problem
Hello list, im having a hughe problem with the RAND() function first of all im using mysql 3.23 so subquerys are impossible. table1 --- | id | content | | 1 | apple | --- relation_table | id | id_table1 | id_table2 | | 1 | 1 | 1 | | 2 | 1 | 2 | table2 - | id | content | | 1 | bear | | 2 | ape | - The result should be somehting like: Query1 (randomly generated): -- | table1.id | table1.content | table2.id | table2.content | | 1| apple | 1| bear | - or Query2 (randomly generated): -- | table1.id | table1.content | table2.id | table2.content | | 1| apple | 2| ape | - thanks for comments / suggestions / solutions -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
