Mega Code Archive
Display all products - including those with no orders
<![CDATA[
mysql>
mysql> CREATE TABLE IF NOT EXISTS products (
-> id INT PRIMARY KEY,
-> name CHAR(20) NOT NULL );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO products (id, name) VALUES (111, "Socket");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO products (id, name) VALUES (222, "Widget");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO products (id, name) VALUES (333, "Sprocket");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> # create a table called "orders" with 3 records
mysql> CREATE TABLE IF NOT EXISTS orders
-> (
-> num INT PRIMARY KEY,
-> product INT,
-> qty INT,
-> client CHAR(20)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO orders (num, product, qty, client) VALUES (3570, 222, 1000, "Archie");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO orders (num, client) VALUES (5223, "Bernie");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO orders (num, product, qty, client) VALUES (4364, 111, 800, "Connie");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT p.name AS Product,
-> o.num AS OrderNumber,
-> o.qty AS Quantity,
-> o.client AS Client
-> FROM products AS p LEFT OUTER JOIN orders AS o
-> ON p.id = o.product ORDER BY p.name;
+----------+-------------+----------+--------+
| Product | OrderNumber | Quantity | Client |
+----------+-------------+----------+--------+
| Socket | 4364 | 800 | Connie |
| Sprocket | NULL | NULL | NULL |
| Widget | 3570 | 1000 | Archie |
+----------+-------------+----------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> # display all orders - including those with no products
mysql> SELECT o.num AS OrderNumber,
-> p.name AS Product,
-> o.qty AS Quantity,
-> o.client AS Client
-> FROM products AS p RIGHT OUTER JOIN orders AS o
-> ON p.id = o.product ORDER BY o.num;
+-------------+---------+----------+--------+
| OrderNumber | Product | Quantity | Client |
+-------------+---------+----------+--------+
| 3570 | Widget | 1000 | Archie |
| 4364 | Socket | 800 | Connie |
| 5223 | NULL | NULL | Bernie |
+-------------+---------+----------+--------+
3 rows in set (0.00 sec)
mysql>
mysql> # delete these sample tables
mysql> DROP TABLE IF EXISTS products;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS orders;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>