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)

The answer is that it depends on the design of the database as to how the data is stored, and it depends on the desired information as to how the information is queried. — Dale K, Jan 29 '19 at 06:08
@DaleBurrell But keeping it to my case why would have join on `salesman_id` would yield incorrect result — sql_learner, Jan 29 '19 at 06:11
Well I can only guess because I don't know the intention behind the design, nor do I know exactly what information you are trying to obtain, but *in general* (if you consider the real world that this data is trying to model) a `customer` places an `order`. A `salesperson` might facilitate an order, but then its possible that an order could have a null salesperson. And a customer might have a default salesperson, but they might not always service that customer... only you know what the data exactly represents. — Dale K, Jan 29 '19 at 06:16
One end of the join should be to the PrimaryKey of a table. When both ends are ForeignKeys (as proposed above using salesman_id) then the results are potentially many-to-many. Lets take the above tables, and look at Order 70002. When joining on salesman_id, it matches to both customer Rimando and Davis--is that what you want? Same for order 70005 and 70008. — donPablo, Jan 29 '19 at 06:59
@donPablo & sql_learner Knowledge of keys or other constraints is not needed to query or join. The meanings of the base tables & result table matter. If there are constraints it just means that some additional expressions also return the right result that wouldn't otherwise. — philipxy, Jan 30 '19 at 04:33
Possible duplicate of [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/questions/33947260/is-there-any-rule-of-thumb-to-construct-sql-query-from-a-human-readable-descript) — philipxy, Jan 30 '19 at 04:34

score 7 · Accepted Answer · answered Jan 29 '19 at 06:19

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.

score 2 · Answer 2 · answered Jan 29 '19 at 06:23

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.

How do I decide on what key to join the tables

2 Answers2