Using a Join to Create a Lookup Table from Descriptive Labels : Simple JOIN « Join « SQL / MySQL

Home
SQL / MySQL
1.Aggregate Functions
2.Backup Load
3.Command MySQL
4.Cursor
5.Data Type
6.Database
7.Date Time
8.Engine
9.Event
10.Flow Control
11.FullText Search
12.Function
13.Geometric
14.I18N
15.Insert Delete Update
16.Join
17.Key
18.Math
19.Procedure Function
20.Regular Expression
21.Select Clause
22.String
23.Table Index
24.Transaction
25.Trigger
26.User Permission
27.View
28.Where Clause
29.XML
SQL / MySQL » Join » Simple JOIN 




Using a Join to Create a Lookup Table from Descriptive Labels
     
mysql>
mysql> CREATE TABLE coin
    -> (
    ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     date CHAR(5NOT NULL, # digits + mint letter
    ->     denom CHAR(20NOT NULL, # denomination (e.g., Lincoln cent)
    ->     PRIMARY KEY (id)
    -> );
Query OK, rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE denom
    -> (
    ->  denom_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  PRIMARY KEY (denom_id)
    -> )
    -> SELECT DISTINCT denom AS name FROM coin;
Query OK, rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> INSERT INTO coin (date,denomVALUES('1944s','Lincoln cent');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1977','Roosevelt dime');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1955d','Lincoln cent');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1938','Jefferson nickel');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1964','Kennedy half dollar');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1959','Lincoln cent');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1945','Jefferson nickel');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1905','Buffalo nickel');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1924','Mercury head dime');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('2010','Roosevelt dime');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1937','Mercury head dime');
Query OK, row affected (0.00 sec)

mysql> INSERT INTO coin (date,denomVALUES('1977','Kennedy half dollar');
Query OK, row affected (0.00 sec)

mysql>
mysql> INSERT INTO denom (nameSELECT DISTINCT denom FROM coin;
Query OK, rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

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

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

   
    
    
    
    
  














Related examples in the same category
1.Using More Than one Table
2.Self join
3.Simple table join
4.Join three tables
5.Query data from two tables
6.JOIN two tables with alias name
7.Using a Join to Control Query Output Order
8.Return the first names and surnames of both the sales rep and the customer, as well as the value of the sale
9.Query data from two tables 2
10.Finding Rows in One Table That Match Rows in Another
11.Identify records from author table that corresponds to the author name, use its a_id value to find matching re
12.Using information in the book table to find information in the author table
13.Finding Rows with No Match in Another Table
14.Shorten the output column list to include only columns from the author table
15.List each author from the author table, and whether or not you have any books by the author
16.Using table alias to qualify column name when column names exist
17.Using table alias to qualify column name
18.PSEUDONYMS FOR TABLE NAMES
19.Qualify the column name with table name
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.