2

I have a table employees:

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| persons_id     | int(10) unsigned |
| restaurants_id | int(10) unsigned | 
+----------------+------------------+

and a table bills:

+----------------+------------------+
| Field          | Type             |
+----------------+------------------+
| id             | int(10) unsigned |
| customers_id   | int(10) unsigned |
| employees_id   | int(10) unsigned |
| restaurants_id | int(10) unsigned |
| date           | date             |
+----------------+------------------+

I want to insert 10,000 rows with an employees_id, restaurant_id and a date into the table bills by using cross joins. The table employees is filled with 15 rows (employees).

Currently I do following:

insert into bills (employees_id, restaurants_id, date) select e.id, e.restaurants_id, '2012-01-01' from employees e cross join employees e2 cross join employees e3 cross join employees e4 order by RAND() limit 10000;

With this query I get 50,625 rows (without to limit to 10,000) (15rows*15rows*15rows*15rows=50,625rows). Is there an more elegant solution to get 10,000 rows? So I don't have to write X times cross join employees eX.

I'm using MySQL. Thanks.

3

No, their is no better way then cross joining on yourself in MySQL. You can make the SQL slightly more elegant using, say, a CTE in other RDBMS, but what you have is the best way IMO.

By "best", I include "efficient" too: set based/no loops, and no temp tables

0
1
CREATE TABLE emp1 SELECT id,restaurants_id FROM employees WHERE 1=2;
INSERT INTO emp1 SELECT id,restaurants_id FROM employees;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
INSERT INTO emp1 SELECT * FROM emp1;
CREATE TABLE emp2 SELECT id,restaurants_id FROM employees WHERE 1=2;
ALTER TABLE emp2 ADD rnd INT NULL;
INSERT INTO emp2 (id,restaurants_id,rnd)
SELECT id,restaurants_id,RAND()*10000 FROM emp1 LIMIT 10000;
ALTER TABLE emp2 ADD INDEX (rnd);
DROP TABLE emp1;
INSERT INTO bills (employees_id, restaurants_id, date)
SELECT id,restaurants_id FROM emp2 ORDER BY rnd;
3
  • you call this more elegant?
    – arminb
    May 7 '12 at 23:51
  • @arminb, maybe it you should define what you mean by elegant :)
    – JHFB
    May 8 '12 at 13:12
  • sorry i thought it's obvious. by elegant i meant a one-liner without creating temporary tables/variables etc. but thank you anyway :)
    – arminb
    May 8 '12 at 13:55
1

You can use WHILE loop to do this.

SET num = 0

WHILE num < 10000 do

    INSERT INTO bills (employees_id, restaurants_id, date)
    SELECT id, restaurants_id, '2012-01-01' FROM employees WHERE id = 1 + RAND() * 16;

    SET num = num + 1

END WHILE;

In the above example, it selects a random employee where his id is between 1 and 15 (you said you have only 15 of them). This assumes that your employee IDs are sequential.

2
  • 1
    You can replace WHERE id = 1 + RAND() * 16 with ORDER BY RAND() LIMIT 1 - so no assumptions are made. May 8 '12 at 7:45
  • thanks for this. but i was looking for a one-line solution. nvm this was also useful for me.
    – arminb
    May 8 '12 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.