Performing a LEFT JOIN on just the customer and sales tables. : Left Join « Join « SQL / MySQL






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

Performing a LEFT JOIN on just the customer and sales tables.

     
mysql>
mysql> CREATE TABLE customer (
    ->   id int(11) default NULL,
    ->   first_name varchar(30) default NULL,
    ->   surname varchar(40) default NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO customer VALUES (1, 'Yvonne', 'Clegg');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES (2, 'Johnny', 'Chaka-Chaka');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES (3, 'Winston', 'Powers');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES (4, 'Patricia', 'Mankunku');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE sales (
    ->   code int(11) default NULL,
    ->   sales_rep int(11) default NULL,
    ->   customer int(11) default NULL,
    ->   value int(11) default NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO sales VALUES (1, 1, 1, 2000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (2, 4, 3, 250);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (3, 2, 3, 500);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (4, 1, 4, 450);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (5, 3, 1, 3800);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales VALUES (6, 1, 2, 500);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE sales_rep (
    ->   employee_number int(11) default NULL,
    ->   surname varchar(40) default NULL,
    ->   first_name varchar(30) default NULL,
    ->   commission tinyint(4) default NULL,
    ->   date_joined date default NULL,
    ->   birthday date default NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO sales_rep VALUES (1, 'Rive', 'Sol', 10,  '2000-02-15', '1976-03-18');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales_rep VALUES (2, 'Gordimer', 'Charlene', 15, '1998-07-09', '1958-11-30');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales_rep VALUES (3, 'Serote', 'Mike', 10, '2001-05-14', '1971-06-18');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sales_rep VALUES (4, 'Rive', 'Mongane', 10, '2002-11-23', '1982-01-04');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> SELECT first_name,surname,value FROM sales LEFT JOIN customer
    ->  ON id=customer;
+------------+-------------+-------+
| first_name | surname     | value |
+------------+-------------+-------+
| Yvonne     | Clegg       |  2000 |
| Winston    | Powers      |   250 |
| Winston    | Powers      |   500 |
| Patricia   | Mankunku    |   450 |
| Yvonne     | Clegg       |  3800 |
| Johnny     | Chaka-Chaka |   500 |
+------------+-------------+-------+
6 rows in set (0.00 sec)

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

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

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








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.Creating the table list with LEFT JOIN and then forming the linking connection with ON
6.A LEFT JOIN and a regular join
7.To list each author record, whether or not there are any book records for it, use a LEFT JOIN
8.To force each category to be displayed, use a reference table and a LEFT JOIN.
9.Creating Left Joins
10.Replacing the ON clause with the USING clause for Left Join
11.Use a left join to link more than two tables.
12.Addition of the LEFT keyword to each join definition
13.Left Joins (Left Outer Joins)
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