Join two tables with char type columns : Join Select « Join « SQL / MySQL

1. SQL / MySQL
2. Join
3. Join Select

Join two tables with char type columns

    
mysql>
mysql> CREATE TABLE Item(
    ->     title_num TINYINT NOT NULL AUTO_INCREMENT,
    ->     title_cust CHAR(4),
    ->     PRIMARY KEY(title_num)
    -> ) ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DESC Item;
+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| title_num  | tinyint(4) | NO   | PRI | NULL    | auto_increment |
| title_cust | char(4)    | YES  |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO Item VALUES
    -> (1, 'Mr.'),
    -> (2, 'Ms.'),
    -> (3, 'Mrs.'),
    -> (4, 'Miss'),
    -> (5, 'Sir'),
    -> (6, 'Dame'),
    -> (7, 'Dr.'),
    -> (8, 'Lady'),
    -> (9, 'None');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE product(
    ->     cust_num MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->     cust_title TINYINT,
    ->     cust_last CHAR(20) NOT NULL,
    ->     cust_first CHAR(15) NOT NULL,
    ->     cust_suffix ENUM('Jr.', 'II', 'III','IV', 'V', 'M.D.','PhD'),
    ->     cust_add1 CHAR(30) NOT NULL,
    ->     cust_add2 CHAR(10),
    ->     cust_city CHAR(18) NOT NULL,
    ->     cust_state CHAR(2) NOT NULL,
    ->     cust_zip1 CHAR(5)NOT NULL,
    ->     cust_zip2 CHAR(4),
    ->     cust_duckname CHAR(25) NOT NULL,
    ->     cust_duckbday DATE,
    ->     PRIMARY KEY (cust_num)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 1, 'XML', 'Red', 'III', '1022 N.E. Sea of Rye', 'A207', 'Seacouver', 'WA', '98601', '3464', 'Netrek Rules'
, '1967:10:21');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 4, 'SQL', 'Vicki', 0, '2004 Singleton Dr.', 0, 'Freedom', 'KS', '67209', '4321', 'Frida Kahlo de Tomayo',
'1948:03:21');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 9, 'HTML', 'Chantel', 0, '1567 Terra Cotta Way', 0,  'Chicago', 'IL', '89129', '4444', 'Bianca', '1971:07:
29');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 7, 'Robert', 'David', 'Sr.', '20113 Open Road Highway', '#6', 'Blacktop', 'AZ', '00606', '1952', 'Harley',
 '1949:08:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 5, 'Kazui', 'Wonko', 'PhD', '42 Cube Farm Lane', 'Gatehouse', 'Vlimpt', 'CA', '45362', 0, 'Fitzwhistle', '
1961:12:04');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 6, 'iPhone', 'Karen', 0, '3113 Picket Fence Lane', 0,  'Fedora', 'VT', '41927', '5698', 'Tess D''urbervill
e', '1948:08:19');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> INSERT INTO product VALUES
    -> (NULL, 8, 'Mac', 'Jenny', 0, '9 Wishing Well Court', 0, 'Meadowlark Hill', 'KS', '67048', '1234', 'Spike', '1961:
03:21');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql>
mysql> SELECT Item.title_cust, product.cust_last,
    -> product.cust_state
    -> FROM Item, product
    -> WHERE Item.title_num=product.cust_title
    -> ORDER BY product.cust_state;
+------------+-----------+------------+
| title_cust | cust_last | cust_state |
+------------+-----------+------------+
| Dr.        | Robert    | AZ         |
| Sir        | Kazui     | CA         |
| None       | HTML      | IL         |
| Miss       | SQL       | KS         |
| Lady       | Mac       | KS         |
| Dame       | iPhone    | VT         |
| Mr.        | XML       | WA         |
+------------+-----------+------------+
7 rows in set (0.00 sec)

mysql>
mysql> drop table product;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table Item;
Query OK, 0 rows affected (0.00 sec)

   
    
    
    
  

Related examples in the same category