What is the correct way to join these tables?

Question

**itemwhse**

item (primary key)
whse (primary key)
...other fields...


**job**

job (primary key)
suffix (primary key)
item
whse
...other fields...

The job table stores information about jobs were an item was produced. These items are produced at a certain warehouse, but then they can be transported to another facility for storage. The itemwhse table is what holds the inventory amounts and item numbers.

I noticed that I get the correct info either way, but I'd like to know which is correct: inner joining on item and whse or inner joining on item only.

---

Sample Data:

**itemwhse**

item        whse        qty
ItemA       BART        1000
ItemA       BEN         1500


**job**

job         suffix      item       whse        qty
foo1        1           ItemA      BART        2500

Example Query:

select

   j.job
   , j.suffix
   , j.item
   , j.whse
   , i.item
   , i.whse

from

   job as j
   inner join itemwhse as i on j.item = i.item

Answer 1

If item+whse is a composite primary key then you should join by both of them.

Itemwhse is an association table that relates Item to Warehouse with multiplicity M:N.

If you join Job with Itemwhse only by item, you get items of certain type in all warehouses, not only in the warehouse in question.