Display all products - including those with no orders : Sort Order « Select Clause « SQL / MySQL

1. SQL / MySQL
2. Select Clause
3. Sort Order

Display all products - including those with no orders

       
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>

   
    
    
    
    
    
    
  

Related examples in the same category