Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 3 down vote accepted

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

share|improve this answer
    
i fear that you might be right. thanks. –  arminb May 8 '12 at 13:58
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;
share|improve this answer
    
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

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.

share|improve this answer
1  
You can replace WHERE id = 1 + RAND() * 16 with ORDER BY RAND() LIMIT 1 - so no assumptions are made. –  ypercube 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

I know you said mysql, but in postgresql you've got common table expressions (CTE) and you can do stuff like this:

WITH RECURSIVE source (counter) AS ( SELECT 1 UNION ALL SELECT counter + 1 FROM source where counter < 10000)

(--Your awesome query goes here)

FROM source;

So the query gets repeated 10,000 times

Does mysql have something alike?

share|improve this answer
    
MySQL, does not have CTEs, unfortunately. –  Aaron Brown May 8 '12 at 11:20
    
after a little research i agree with @AaronBrown. thanks anyways –  arminb May 8 '12 at 13:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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