How to have multiple rows reference the same field of a single row?

Question

I'm new to PostgreSQL and forming relationships between tables. I have a jobs table and an items table. The jobs table has a shipping_date. I would like the items table to have a column that reflects the shipping_date of the jobs table:

jobs table:

job_id | shipping_date
10001  | 29-06-2014
10002  | 29-06-2014
10003  | 30-06-2014


items table:

item_id | job_id      | shipping_date
1234    | fkey(10001) | need this to be 29-06-2014 
1235    | 10001       | need this to be 29-06-2014

Shipping date can't be a fkey as it is not unique but we need it to reference the relevant shipping_date from jobs table.

Each job can have several associated items. Each item can only be associated with one job.

Thank you!

Answer 1

You don't want to have redundancy in your database. You could create a view that joins the two tables and show you what you need. The select could be:

SELECT a.item_id, a.job_id, b.shipping_date
  FROM items a INNER JOIN jobs b ON a.job_id = b.job_id

Answer 2

I found that I just needed to do a join clause:

SELECT shipping_date FROM items INNER JOIN jobs ON (job_items.job_id = jobs.job_id);