my brief has changed slightly, the tables are all basically the same though.
This is how my tables were created:
create table flock (flock_number int, flock_location text, primary key(flock_number));
create table sheep (sheep_number int, tag_method text, flock_id int, sex text, breed text, age int, parent_id int, born_on_farm text, date_joined date, price_paid int, price_sold int, primary key(sheep_number), foreign key (flock_id)references flock(flock_number));
create table movements (sheep_number int, movement_code text, movement_date date, movement_from text, movement_to text, movement_carrier text, current_location text, foreign key(sheep_number) references sheep(sheep_number));
create table treatments (sheep_number int, treatment_type text, treatment_date date, treatment_by text, treatment_reason text,treatment_code text, foreign key(sheep_number) references sheep(sheep_number));
create table contacts (sheep_number int, contact_code text, contact_date date, contact_substance text, foreign key(sheep_number) references sheep(sheep_number));
this is what i tried to perform the join:
select s.sheep_number,
s.tag_method,
s.flock_id,
s.sex,
s.breed,
s.age,
s.parent_id,
s.born_on_farm,
s.date_joined,
s.price_paid,
s.price_sold,
t.sheep_number,
t.treatment_type,
t.treatment_date,
t.treatment_by,
t.treatment_reason,
t.treatment_code,
c.sheep_number,
c.contact_code,
c.contact_date,
c.contact_substance,
m.sheep_number,
m.movement_code,
m.movement_date,
m.movement_from,
m.movement_to,
m.movement_carrier,
m.current_location
from flock f
join sheep s
on (f.flock_number=s.flock_id)
join contacts c
on (s.sheep_number=c.sheep_number)
join movements m
on (s.sheep_number=m.sheep_number)
join treatments t
on (s.sheep_number=s.sheep_number)
this seemed to work in that it did not spit out any errors. the natural joins still will not work though. the thing that i ultimately want to acheive is to be able to perform a select which will allow me to display all the details held in the db for each sheep, (all the details from all the different tables in one row). being that this is only for a prototype system the db size will remain small at this time. cheers for the help.
