Return the first names and surnames of both the sales rep and the customer, as well as the value of the sale : Simple JOIN « Join « SQL / MySQL






  1. SQL / MySQL
  2. Join
  3. Simple JOIN

Return the first names and surnames of both the sales rep and the customer, as well as the value of the 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.01 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 sales_rep.first_name,sales_rep.surname,
    ->  value,customer.first_name, customer.surname FROM
    ->  sales,sales_rep,customer WHERE sales_rep.employee_number =
    ->  sales.sales_rep AND customer.id = sales.customer;
+------------+----------+-------+------------+-------------+
| first_name | surname  | value | first_name | surname     |
+------------+----------+-------+------------+-------------+
| Sol        | Rive     |  2000 | Yvonne     | Clegg       |
| Mongane    | Rive     |   250 | Winston    | Powers      |
| Charlene   | Gordimer |   500 | Winston    | Powers      |
| Sol        | Rive     |   450 | Patricia   | Mankunku    |
| Mike       | Serote   |  3800 | Yvonne     | Clegg       |
| Sol        | Rive     |   500 | Johnny     | Chaka-Chaka |
+------------+----------+-------+------------+-------------+
6 rows in set (0.00 sec)

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








Related examples in the same category

1.Using More Than one Table
2.Self join
3.Simple table join
4.Join three tables
5.Query data from two tables
6.JOIN two tables with alias name
7.Using a Join to Control Query Output Order
8.Using a Join to Create a Lookup Table from Descriptive Labels
9.Query data from two tables 2
10.Finding Rows in One Table That Match Rows in Another
11.Identify records from author table that corresponds to the author name, use its a_id value to find matching re
12.Using information in the book table to find information in the author table
13.Finding Rows with No Match in Another Table
14.Shorten the output column list to include only columns from the author table
15.List each author from the author table, and whether or not you have any books by the author
16.Using table alias to qualify column name when column names exist
17.Using table alias to qualify column name
18.PSEUDONYMS FOR TABLE NAMES
19.Qualify the column name with table name