CODEWITHSUNDEEP
mysqljoinrandomlimit
Michael
Michael

Reputation: 107

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?

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!

Upvotes: 2

Views: 161

Answers (5)

DRapp
DRapp

Reputation: 48139

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

Upvotes: 2

Karolis
Karolis

Reputation: 9562

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

Upvotes: 0

Bohemian
Bohemian

Reputation: 425003

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

Upvotes: 0

Question Mark
Question Mark

Reputation: 3606

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?

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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.

Upvotes: 0

Related Questions