JOINs Across Two Tables : Join Select « Join « SQL / MySQL






  1. SQL / MySQL
  2. Join
  3. Join Select

JOINs Across Two Tables

    
mysql>
mysql> CREATE TABLE titles (
    ->   titleID int(11),
    ->   title varchar(100),
    ->   subtitle varchar(100),
    ->   edition tinyint(4),
    ->   publID int(11),
    ->   catID int(11),
    ->   langID int(11),
    ->   year int(11),
    ->   isbn varchar(20),
    ->   comment varchar(255),
    ->   ts timestamp,
    ->   authors varchar(255),
    ->   PRIMARY KEY  (titleID)
    -> );
ERROR 1050 (42S01): Table 'titles' already exists
mysql>
mysql>
mysql>
mysql> INSERT INTO titles VALUES (1,'Linux','Installation',5,1,57,2,2000,NULL,NULL,'2005-02-28 13:34:21','Michael'),
    ->                           (2,'Excel',NULL,NULL,2,3,NULL,2000,NULL,NULL,'2005-02-28 13:34:22','David'),
    ->                           (3,'XML',NULL,NULL,1,2,NULL,1997,NULL,NULL,'2005-02-28 13:34:22','Edwards'),
    ->                           (4,'PHP',NULL,NULL,3,6,NULL,2000,NULL,NULL,'2005-02-28 13:34:22','Tom'),
    ->                           (5,'MySQL','',0,3,34,NULL,2000,'','','2005-02-28 13:34:22','Paul'),
    ->                           (6,'Java',NULL,NULL,4,34,NULL,1999,NULL,NULL,'2005-02-28 13:34:22','Tim');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>
mysql> CREATE TABLE publishers (
    ->   publID int(11) NOT NULL auto_increment,
    ->   publName varchar(60) collate latin1_german1_ci NOT NULL default '',
    ->   ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   PRIMARY KEY  (publID),
    ->   KEY publName (publName)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO publishers VALUES (1,'A','2004-12-02 18:36:58'),
    ->                                 (2,'Apress','2004-12-02 18:36:58'),
    ->                                 (3,'New Riders','2004-12-02 18:36:58'),
    ->                                 (4,'O\'Reilly & Associates','2004-12-02 18:36:58'),
    ->                                 (5,'Hanser','2004-12-02 18:36:58');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT title, publName FROM titles, publishers;
+-------+-----------------------+
| title | publName              |
+-------+-----------------------+
| Linux | A                     |
| Linux | Apress                |
| Linux | Hanser                |
| Linux | New Riders            |
| Linux | O'Reilly & Associates |
| Excel | A                     |
| Excel | Apress                |
| Excel | Hanser                |
| Excel | New Riders            |
| Excel | O'Reilly & Associates |
| XML   | A                     |
| XML   | Apress                |
| XML   | Hanser                |
| XML   | New Riders            |
| XML   | O'Reilly & Associates |
| PHP   | A                     |
| PHP   | Apress                |
| PHP   | Hanser                |
| PHP   | New Riders            |
| PHP   | O'Reilly & Associates |
| MySQL | A                     |
| MySQL | Apress                |
| MySQL | Hanser                |
| MySQL | New Riders            |
| MySQL | O'Reilly & Associates |
| Java  | A                     |
| Java  | Apress                |
| Java  | Hanser                |
| Java  | New Riders            |
| Java  | O'Reilly & Associates |
+-------+-----------------------+
30 rows in set (0.00 sec)

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

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








Related examples in the same category

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