Simple table join : Simple JOIN « Join « SQL / MySQL






Simple table join

  
/*
mysql> CREATE TABLE Articles (
    ->    ArticleID SMALLINT NOT NULL PRIMARY KEY,
    ->    ArticleTitle VARCHAR(60) NOT NULL,
    ->    Copyright YEAR NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO Articles VALUES (12786, 'How write a paper', 1934),
    ->                             (13331, 'Publish a paper', 1919),
    ->                             (14356, 'Sell a paper', 1966),
    ->                             (15729, 'Buy a paper', 1932),
    ->                             (16284, 'Conferences', 1996),
    ->                             (17695, 'Journal', 1980),
    ->                             (19264, 'Information', 1992),
    ->                             (19354, 'AI', 1993);
Query OK, 8 rows affected (0.08 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE Authors (
    ->    AuthID SMALLINT NOT NULL PRIMARY KEY,
    ->    AuthorFirstName VARCHAR(20),
    ->    AuthorMiddleName VARCHAR(20),
    ->    AuthorLastName VARCHAR(20)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson'),
    ->                            (1007, 'Jason', 'Carol', 'Oak'),
    ->                            (1008, 'James', NULL, 'Elk'),
    ->                            (1009, 'Tom', 'M', 'Ride'),
    ->                            (1010, 'Jack', 'K', 'Ken'),
    ->                            (1011, 'Mary', 'G.', 'Lee'),
    ->                            (1012, 'Annie', NULL, 'Peng'),
    ->                            (1013, 'Alan', NULL, 'Wang'),
    ->                            (1014, 'Nelson', NULL, 'Yin');
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE AuthorArticle (
    ->    AuthID SMALLINT NOT NULL,
    ->    ArticleID SMALLINT NOT NULL,
    ->    PRIMARY KEY (AuthID, ArticleID),
    ->    FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
    ->    FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.23 sec)

mysql> INSERT INTO AuthorArticle VALUES (1006, 14356),
    ->                               (1008, 15729),
    ->                               (1009, 12786),
    ->                               (1010, 17695),
    ->                               (1011, 15729),       (1012, 19264),
    ->                               (1012, 19354),
    ->                               (1014, 16284);
Query OK, 8 rows affected (0.07 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT ArticleTitle, Copyright, AuthID
    -> FROM Articles AS b, AuthorArticle AS ab
    -> WHERE b.ArticleID=ab.ArticleID
    -> ORDER BY ArticleTitle;
+-------------------+-----------+--------+
| ArticleTitle      | Copyright | AuthID |
+-------------------+-----------+--------+
| AI                |      1993 |   1012 |
| Buy a paper       |      1932 |   1008 |
| Buy a paper       |      1932 |   1011 |
| Conferences       |      1996 |   1014 |
| How write a paper |      1934 |   1009 |
| Information       |      1992 |   1012 |
| Journal           |      1980 |   1010 |
| Sell a paper      |      1966 |   1006 |
+-------------------+-----------+--------+
8 rows in set (0.02 sec)

*/
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;



CREATE TABLE Articles (
   ArticleID SMALLINT NOT NULL PRIMARY KEY,
   ArticleTitle VARCHAR(60) NOT NULL,
   Copyright YEAR NOT NULL
)
ENGINE=INNODB;


INSERT INTO Articles VALUES (12786, 'How write a paper', 1934),
                            (13331, 'Publish a paper', 1919),
                            (14356, 'Sell a paper', 1966),
                            (15729, 'Buy a paper', 1932),
                            (16284, 'Conferences', 1996),
                            (17695, 'Journal', 1980),
                            (19264, 'Information', 1992),
                            (19354, 'AI', 1993);


CREATE TABLE Authors (
   AuthID SMALLINT NOT NULL PRIMARY KEY,
   AuthorFirstName VARCHAR(20),
   AuthorMiddleName VARCHAR(20),
   AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;


INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson'),
                           (1007, 'Jason', 'Carol', 'Oak'),
                           (1008, 'James', NULL, 'Elk'),
                           (1009, 'Tom', 'M', 'Ride'),
                           (1010, 'Jack', 'K', 'Ken'),
                           (1011, 'Mary', 'G.', 'Lee'),
                           (1012, 'Annie', NULL, 'Peng'),
                           (1013, 'Alan', NULL, 'Wang'),
                           (1014, 'Nelson', NULL, 'Yin');


CREATE TABLE AuthorArticle (
   AuthID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   PRIMARY KEY (AuthID, ArticleID),
   FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO AuthorArticle VALUES (1006, 14356), 
                              (1008, 15729), 
                              (1009, 12786), 
                              (1010, 17695),
                              (1011, 15729), 
                              (1012, 19264), 
                              (1012, 19354), 
                              (1014, 16284);
  
SELECT ArticleTitle, Copyright, AuthID
FROM Articles AS b, AuthorArticle AS ab
WHERE b.ArticleID=ab.ArticleID
ORDER BY ArticleTitle;

           
         
    
  








Related examples in the same category

1.Using More Than one Table
2.Self join
3.Join three tables
4.Query data from two tables
5.JOIN two tables with alias name
6.Using a Join to Control Query Output Order
7.Using a Join to Create a Lookup Table from Descriptive Labels
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