Mega Code Archive
To select a single winner at random from the combined results of the three tables
<![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
-> UNION
-> SELECT CONCAT(last_name,', ',first_name), address FROM customer
-> UNION
-> SELECT company, street FROM vendor
-> ORDER BY RAND( ) LIMIT 1;
+-----------------+------------------+
| name | addr |
+-----------------+------------------+
| Peterson, Grace | 16055 First Ave. |
+-----------------+------------------+
1 row in set (0.00 sec)
mysql>
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)
]]>