Creating the table list with LEFT JOIN and then forming the linking connection with ON

     
mysql>
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)
    -> );
Query OK, 0 rows affected (0.00 sec)

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');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

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> SELECT title, publName
    -> FROM titles LEFT JOIN publishers
    -> ON titles.publID = publishers.publID;
+-------+-----------------------+
| title | publName              |
+-------+-----------------------+
| Linux | A                     |
| Excel | Apress                |
| XML   | A                     |
| PHP   | New Riders            |
| MySQL | New Riders            |
| Java  | O'Reilly & Associates |
+-------+-----------------------+
6 rows in set (0.00 sec)

mysql>
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)

mysql>

Related examples in the same category

1.	LEFT JOIN tables
2.	'USING' command in LEFT JOIN
3.	Two LEFT JOIN in select command
4.	Two LEFT JOIN
5.	A LEFT JOIN and a regular join
6.	To list each author record, whether or not there are any book records for it, use a LEFT JOIN
7.	To force each category to be displayed, use a reference table and a LEFT JOIN.
8.	Creating Left Joins
9.	Replacing the ON clause with the USING clause for Left Join
10.	Use a left join to link more than two tables.
11.	Addition of the LEFT keyword to each join definition
12.	Left Joins (Left Outer Joins)
13.	Performing a LEFT JOIN on just the customer and sales tables.
14.	Table order in a LEFT JOIN is important.
15.	Left outer join syntax
16.	Left outer join then order
17.	Left outer join
18.	Left outer join with using clause
19.	Left outer join with subquery
20.	Query from left outer join
21.	To return all the sales reps who have not yet made a sale

Creating the table list with LEFT JOIN and then forming the linking connection with ON : Left Join « Join « SQL / MySQL

Related examples in the same category