display each part number, name and price

      
mysql>
mysql> CREATE TABLE IF NOT EXISTS parts
    -> (
    ->   num    INT             PRIMARY KEY,
    ->   name   CHAR(20)        NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> # insert 3 records into the "parts" table
mysql> INSERT INTO parts (num, name)   VALUES (382131, "Standard bracket");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO parts (num, name)   VALUES (382132, "Slide bracket");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO parts (num, name)   VALUES (382133, "Low-mount bracket");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> # create a table called "parts_prices"
mysql> CREATE TABLE IF NOT EXISTS parts_prices
    -> (
    ->   num    INT             PRIMARY KEY,
    ->   price  DECIMAL(6,2)    NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> # insert 3 records into the "parts_prices" table
mysql> INSERT INTO parts_prices (num, price)   VALUES (382131, 8.99);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO parts_prices (num, price)   VALUES (382132, 10.99);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO parts_prices (num, price)   VALUES (382133, 29.99);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> # display all data in the "parts" and "parts_prices" table
mysql> SELECT * FROM parts;
+--------+-------------------+
| num    | name              |
+--------+-------------------+
| 382131 | Standard bracket  |
| 382132 | Slide bracket     |
| 382133 | Low-mount bracket |
+--------+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM parts_prices;
+--------+-------+
| num    | price |
+--------+-------+
| 382131 |  8.99 |
| 382132 | 10.99 |
| 382133 | 29.99 |
+--------+-------+
3 rows in set (0.00 sec)

mysql>
mysql> SELECT   p.*, pp.price
    -> FROM     parts AS p, parts_prices AS pp
    -> WHERE    p.num = pp.num;
+--------+-------------------+-------+
| num    | name              | price |
+--------+-------------------+-------+
| 382131 | Standard bracket  |  8.99 |
| 382132 | Slide bracket     | 10.99 |
| 382133 | Low-mount bracket | 29.99 |
+--------+-------------------+-------+
3 rows in set (0.00 sec)

mysql>
mysql> # delete these sample tables
mysql> DROP TABLE IF EXISTS parts;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS parts_prices;
Query OK, 0 rows affected (0.00 sec)

mysql>

Related examples in the same category

1.	NATURAL JOIN in MySQL
2.	Two NATURAL JOIN in select command
3.	The most expensive book per author
4.	Calculating Differences Between Successive Rows
5.	Shows the daily and cumulative precipitation for each day
6.	Display the all members of staff in the same department as Tony West

display each part number, name and price : Join Table « Join « SQL / MySQL

Related examples in the same category