SQL Exercise: Join within the tables salesman, customer and orders
7. Join All Tables Uniquely
Write a SQL statement to join the tables salesman, customer and orders so that the same column of each table appears once and only the relational rows are returned.
Sample table: orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample table: customer
customer_id | cust_name | city | grade | salesman_id
-------------+----------------+------------+-------+-------------
3002 | Nick Rimando | New York | 100 | 5001
3007 | Brad Davis | New York | 200 | 5001
3005 | Graham Zusi | California | 200 | 5002
3008 | Julian Green | London | 300 | 5002
3004 | Fabian Johnson | Paris | 300 | 5006
3009 | Geoff Cameron | Berlin | 100 | 5003
3003 | Jozy Altidor | Moscow | 200 | 5007
3001 | Brad Guzan | London | | 5005
Sample table: salesman
salesman_id | name | city | commission
-------------+------------+----------+------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome | 0.13
5003 | Lauson Hen | San Jose | 0.12
Sample Solution:
-- Selecting all columns from the result of natural joins between three tables: 'orders', 'customer', and 'salesman'
SELECT *
-- Performing a natural join between 'orders' and 'customer' tables
FROM orders
NATURAL JOIN customer
-- Performing another natural join with the result of the previous join and the 'salesman' table
NATURAL JOIN salesman;
Output of the Query:
salesman_id city customer_id ord_no purch_amt ord_date cust_name grade name commission 5005 London 3001 70009 270.65 2012-09-10 Brad Guzan Pit Alex 0.11 5001 New York 3002 70002 65.26 2012-10-05 Nick Rimando 100 James Hoog 0.15 5001 New York 3007 70005 2400.60 2012-07-27 Brad Davis 200 James Hoog 0.15 5001 New York 3002 70008 5760.00 2012-09-10 Nick Rimando 100 James Hoog 0.15 5006 Paris 3004 70010 1983.43 2012-10-10 Fabian Johnson 300 Mc Lyon 0.14 5001 New York 3002 70013 3045.60 2012-04-25 Nick Rimando 100 James Hoog 0.15
Explanation:
The said SQL query that uses the NATURAL JOIN clause to combine rows from three tables: orders, customer, and salesman.
The NATURAL JOIN clause compares all columns of the two joined tables and only returns the rows where the values match.
The query retrieves all columns from the three tables where the customer_id from the orders table matches the customer_id from the customer table, and the salesman_id from the orders table matches the salesman_id from the salesman table.
Note that using NATURAL JOIN can be dangerous if the tables have columns with the same name but different meanings, because it could lead to unexpected results.
Relational Algebra Expression:
Relational Algebra Tree:
Explanation:
Visual Explanation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
For more Practice: Solve these Related Problems:
- Write a SQL query to join the tables salesman, customer, and orders so that common columns appear only once and only matching rows are returned.
- Write a SQL query to perform an inner join on salesman, customer, and orders, and display distinct columns without duplication.
- Write a SQL query to merge data from the three tables based on customer_id and salesman_id, ensuring no repeated columns.
- Write a SQL query to return a consolidated report by joining salesman, customer, and orders, where each common column appears only once.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Display commission of the salesman for an order.
Next SQL Exercise: Customer who works either through a salesman or by own.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.