JOIN three tables with conditions : Join Three Tables « 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 » Join Three Tables 




JOIN three tables with conditions

/*

mysql> SELECT STRAIGHT_JOIN ArticleTitle, Copyright,
    ->    CONCAT_WS(' ', AuthorFirstName, AuthorMiddleName, AuthorLastName) AS A
uthor
    -> FROM Articles AS b, AuthorArticle AS ab, Authors AS a
    -> WHERE b.ArticleID=ab.ArticleID AND ab.AuthID=a.AuthID AND Copyright<1980
    -> ORDER BY ArticleTitle;
+-------------------+-----------+-------------------+
| ArticleTitle      | Copyright | Author            |
+-------------------+-----------+-------------------+
| Buy a paper       |      1932 | James Elk         |
| Buy a paper       |      1932 | Mary G. Lee       |
| How write a paper |      1934 | Tom M Ride        |
| Sell a paper      |      1966 | Henry S. Thompson |
+-------------------+-----------+-------------------+
4 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(60NOT 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 (AuthIDREFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleIDREFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO AuthorArticle VALUES (100614356)
                              (100815729)
                              (100912786)
                              (101017695),
                              (101115729)
                              (101219264)
                              (101219354)
                              (101416284);
  
SELECT STRAIGHT_JOIN ArticleTitle, Copyright,
   CONCAT_WS(' ', AuthorFirstName, AuthorMiddleName, AuthorLastNameAS Author
FROM Articles AS b, AuthorArticle AS ab, Authors AS a
WHERE b.ArticleID=ab.ArticleID AND ab.AuthID=a.AuthID AND Copyright<1980
ORDER BY ArticleTitle;


           
       














Related examples in the same category
1.JOIN three tables with alias 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.