To return all the sales reps who have not yet made a sale : Left Join « Join « SQL / MySQL






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

To return all the sales reps who have not yet made a sale

      
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, 'Tom', 'Clegg');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES (2, 'Jack', 'Smith');
Query OK, 1 row affected (0.00 sec)

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

mysql> INSERT INTO customer VALUES (4, 'Peter', 'Miller');
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, 'Ray', 'Sol', 10,  '2000-02-15', '1976-03-18');
Query OK, 1 row affected (0.00 sec)

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

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

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

mysql>
mysql>
mysql> SELECT first_name,surname FROM sales_rep WHERE employee_number
    ->  NOT IN (SELECT DISTINCT code from sales);
Empty set (0.00 sec)

mysql>
mysql> SELECT DISTINCT first_name,surname FROM sales_rep LEFT JOIN sales ON
    ->  sales_rep=employee_number WHERE sales_rep IS NULL;
Empty set (0.00 sec)

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)

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.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.Performing a LEFT JOIN on just the customer and sales tables.
15.Table order in a LEFT JOIN is important.
16.Left outer join syntax
17.Left outer join then order
18.Left outer join
19.Left outer join with using clause
20.Left outer join with subquery
21.Query from left outer join