How do I decide on what key to join the tables

Question

I have two tables: orders and customer

orders:

+--------+-----------+------------+-------------+-------------+
| ord_no | purch_amt |  ord_date  | customer_id | salesman_id |
+--------+-----------+------------+-------------+-------------+
|  70009 |    270.65 | 2012-09-10 |        3001 |        5005 |
|  70002 |     65.26 | 2012-10-05 |        3002 |        5001 |
|  70004 |    110.50 | 2012-08-17 |        3009 |        5003 |
|  70005 |   2400.60 | 2012-07-27 |        3007 |        5001 |
|  70008 |   5760.00 | 2012-09-10 |        3002 |        5001 |
|  70010 |   1983.43 | 2012-10-10 |        3004 |        5006 |
|  70003 |   2480.40 | 2012-10-10 |        3009 |        5003 |
|  70011 |     75.29 | 2012-08-17 |        3003 |        5007 |
|  70013 |   3045.60 | 2012-04-25 |        3002 |        5001 |
|  70001 |    150.50 | 2012-10-05 |        3005 |        5002 |
|  70007 |    948.50 | 2012-09-10 |        3005 |        5002 |
|  70012 |    250.45 | 2012-06-27 |        3008 |        5002 |
+--------+-----------+------------+-------------+-------------+

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 |
+-------------+----------------+------------+-------+-------------+

The problem I am trying to solve is

Write a SQL statement to make a list with order no, purchase amount, customer name and their cities for those orders which order amount between 500 and 2000.

My Query for that is

SELECT ord_no, purch_amt, cust_name, city
FROM orders o
JOIN customer c
ON o.customer_id = c.customer_id
WHERE purch_amt BETWEEN 500 AND 2000

The above query gives correct answer as per this link.But I am not understanding on what column to join.

I have joined tables on o.customer_id = c.customer_id but what If I join both tables on salesman_id.

Why join on customer_id is correct and salesman_id is wrong. I wanted to understand in general how to decide columns on which join should occur when there are more than one common columns in tables between which join occurs( in this case customer_id and salesman_id)

Answer 1

The question to answer: What does customer.salesman_id represent?

orders.salesman_id obviously tells us which salesman sold the stuff, while orders.customer_id tells us who bought it. So we join by customer_id in order to get the customer name for an order.

customer.salesman_id may mean something like the salesman who usually sells to the customer, which would be completely unrelated to the task.

It could also be, however, that a customer_id is only valid in combination with a salesman_id. So salesman #1 has a customer #1, but salesman #2 also has (another) customer #1. That would be composite keys, i.e. salesman_id + customer_id would be the customer's primary key and naturally the orders' foreign key. You'd have to join on both columns then. (However, it would be a bad idea then to call these columns ID. salesman_number + customer_number would be a better choice then, to indicate that they are not the tables' single IDs.)

So you see, it depends on the meaning of customer.salesman_id. In order to write queries, you must know your data model. As you say your solution was correct, customer.salesman_id obviously has a meaning unrelated to the given task.

Answer 2

You use a relational database, where there is a relation between the orders and the customers (who placed these orders). There is also a relation to the salesman who handled each order. Depending on the information you need you need to join tables, and relevant details are linked by their key, their unique identifier in each table.