Mega Code Archive
To select a single winner from each table and combine the results
<![CDATA[
mysql>
mysql>
mysql> CREATE TABLE prospect
-> (
-> fname CHAR(40),
-> lname CHAR(40),
-> addr CHAR(40)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE customer
-> (
-> last_name CHAR(40),
-> first_name CHAR(40),
-> address CHAR(40)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE vendor
-> (
-> company CHAR(60),
-> street CHAR(30)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO prospect (fname, lname, addr)
-> VALUES
-> ('Peter','Jones','482 Main St., Apt. 402'),
-> ('Bernice','Smith','916 Maple Dr.')
-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO customer (first_name, last_name, address)
-> VALUES
-> ('Grace','Peterson','16055 First Ave.'),
-> ('Bernice','Smith','916 Maple Dr.'),
-> ('Walter','Brown','8602 1st St.')
-> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO vendor (company, street)
-> VALUES
-> ('Database, Inc.','38 Third Ave.'),
-> ('Xml, Ltd.','213B Commerce Park.')
-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> (SELECT CONCAT(lname,', ',fname) AS name, addr
-> FROM prospect ORDER BY RAND( ) LIMIT 1)
-> UNION
-> (SELECT CONCAT(last_name,', ',first_name), address
-> FROM customer ORDER BY RAND( ) LIMIT 1)
-> UNION
-> (SELECT company, street
-> FROM vendor ORDER BY RAND( ) LIMIT 1);
+---------------+------------------------+
| name | addr |
+---------------+------------------------+
| Jones, Peter | 482 Main St., Apt. 402 |
| Brown, Walter | 8602 1st St. |
| Xml, Ltd. | 213B Commerce Park. |
+---------------+------------------------+
3 rows in set (0.00 sec)
mysql>
mysql> drop table prospect;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table customer;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table vendor;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>