Having a bit of trouble with MySQL query to receive random combinations from 3 tables

Having a bit of trouble with MySQL query to receive random combinations from 3 tables

This was the original question:

Alright, here is my issue, I have two tables, one named firstnames and the other named lastnames. What I am trying to do here is to find 100 of the possible combinations from these names for test data. The firstnames table has 5494 entries in a single column, and the lastnames table has 88799 entries in a single column. The only query that I have been able to come up with that has some results is:

SELECT * FROM      (SELECT * FROM firstnames ORDER BY rand()) f      LEFT JOIN           (SELECT * FROM lastnames   ORDER BY rand()) l ON 1=1 limit 10;    The problem with this code is  

that it selects 1 firstname and gives every lastname that could go with it. While this is plausible, I will have to set the limit to 500000000 in order to get all the combinations possible without having only 20 first names(and I'd rather not kill my server). However, I only need 100 random generations of entries for test data, and I will not be able to get that with this code. Can anyone please give me any advice?

• The above question has already been answered, I need advice on the question below. I am just providing it as a background.

I want to join another table to the mix named status. This table has 5 entries in one column, and whenever I attempt to join it to the other two, it ends up repeating the two other tables over again so that the status tags fit with every one of them. The only one that I am getting mild success with is this:

SELECT *  FROM ( SELECT firstnames FROM firstnames ORDER BY RAND( )  LIMIT 5 ) AS First  JOIN ( SELECT lastnames FROM lastnames ORDER BY RAND( )  LIMIT 5 ) as Last   JOIN ( SELECT status FROM status ORDER BY RAND( ) LIMIT 1) AS Status ON 1=1;  

While the first and last names do not repeat in this query, only one status tag is listed with each of them. Thank you guys very much for your help!

Answer by ypercube?? for Having a bit of trouble with MySQL query to receive random combinations from 3 tables

I would first add an (automunbered) id field on both tables.

Then I would write a stored procedure that:

• Creates a temporary table randomid with 2 fields (firstid, lastid).

• Inserts 100 rows (or the number you need) with random integers in the two fields, (from 1 to MAX(firstnames.id) into the first field and from 1 to MAX(lastnames.id) to the second one).

• Joins the firstnames and lastnames tables to randomid

• Drops the temp table.

Answer by Question Mark for Having a bit of trouble with MySQL query to receive random combinations from 3 tables

How about turning it on it's head:

SELECT f.name,l.name FROM lastnames l INNER JOIN firstnames f ON 1=1 ORDER BY rand() limit 100;  

Inner joins being faster than left joins etc?

Answer by Bohemian for Having a bit of trouble with MySQL query to receive random combinations from 3 tables

I have no idea what your status table does, but this will get you random names:

select firstnames, lastnames  from (select firstnames from firstnames order by rand() limit 100) fn  cross join (select lastnames from lastnames order by rand() limit 100) ln  order by rand()  limit 100;  

The aliased selects are there so the query returns in this lifetime - there are 10000 rows in the cross join... manageable. Without them there would be gazillions of rows - query wouldn't come back with your row counts

Answer by DRapp for Having a bit of trouble with MySQL query to receive random combinations from 3 tables

Using MySQL Variables, you should be able to accomplish via something like... Although not explicitly tested, this SHOULD get you what you want. You can't do a simple Cartesian cross-join because as you know it will get the first FirstName and join with ALL the Last Names, then the NEXT First Name with all the last names...

This query uses MySQL variables. The inner query (pre respective first and last names) will pre-query your random 10 first (or last) names. Then, join that to a @variable for first name sequence (@fns) and last name sequence (@lns). Since both will have only 10 records, and each will both start with their counter at 0, they will BOTH result in records with a sequence from 1-10 and thus the JOIN will be on the SEQUENCE where only one of each guaranteed value will occur... vs rand() which returns some floating point fraction where you are never guaranteed a number from one table will match that of another.

select        First10.FirstName,        Last10.LastName,        ( SELECT status FROM status ORDER BY RAND( ) LIMIT 1) AS Status     from         ( select fn.FirstName,                 @fns := @fns + 1 as Sequence             from               ( select FirsName,                    from FirstNames                    order by rand()                     limit 10 ) fn,               (select @fns := 0 ) vars        ) First10          JOIN          ( select ln.LastName,                 @lns := @lns + 1 as Sequence             from                ( select LastName,                     from LastNames                     order by rand()                      limit 10 ) ln,                (select @lns := 0 ) vars         ) Last10          ON First10.Sequence = Last10.Sequence  

Answer by Karolis for Having a bit of trouble with MySQL query to receive random combinations from 3 tables

This should solve the status problem:

SELECT      First.firstnames firstname      Last.lastnames lastname      ( SELECT status FROM status ORDER BY RAND() LIMIT 1 ) status  FROM       ( SELECT firstnames FROM firstnames ORDER BY RAND()  LIMIT 5 ) First      JOIN ( SELECT lastnames FROM lastnames ORDER BY RAND()  LIMIT 5 ) Last  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72