Mega Code Archive
Join two tables with char type columns
<![CDATA[
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)
]]>