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






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

1.Simple Join two tables
2.JOINs Across Two Tables
3.JOINs Across Two Tables with link id
4.JOINs Across Three or More Tables
5.Table joins and where clause
6.Select distinct column value during table join
7.Select distinct column values in table join
8.Count joined table
9.Performing a Join Between Tables in Different Databases
10.LINESTRING type column: One or more linear segments joining two points; one-dimensional.
11.Select other columns from rows containing a minimum or maximum value is to use a join.
12.Retrieve the overall summary into another table, then join that with the original table:
13.Tests a different column in the book table to find the initial set of records to be joined with the author tab
14.Select the maximum population value into a temporary table, Then join the temporary table to the original one
15.Creating a temporary table to hold the maximum price, and then joining it with the other tables:
16.To display the authors by name rather than ID, join the book table to the author table
17.To display the author names, join the result with the author table
18.The summary is written to a temporary table, which then is joined to the cat_mailing table to produce the reco
19.Addition of a WHERE clause for table join
20.Join more than two tables together.
21.Creating Straight Joins: STRAIGHT_JOIN
22.Use the basic join syntax and you specify the STRAIGHT_JOIN table option in the SELECT clause
23.Creating Natural Joins
24.Joining Columns with CONCAT
25.A basic join
26.Rewriting Sub-selects as Joins
27.Display game code, name, price and vendor name for each game in the two joined tables
28.Get the player number, the sex, and the name of each player who joined the club after 1980.
29.Convert subqueries to JOINs
30.Join with another database
31.Join two tables with shared columns values
32.Qualify column name with table name during the table join
33.Three tables join together
34.Compare date type value during table join
35.Join on syntax
36.Natural join syntax
37.Join with Integer type column
38.Join three table together