EASY POINTS: i have two tables, same data but relating to different relations, want to combine in report

Hi

Iam using Oracle 9i and forms and reports 6i.  

I have 4 tables, livery_horse, riding_horse, livery_horse_meal, riding_horse_meal

the meal tables relate to the horses and both to a feed table which contains the name of feed etc.

Basically, I want a report of the feed and the sum of what is required for horse_meals, by feed.
I can easily do this for each type of horse, but i want to basically combine the horse_meal tables with a sum on the feed amounts and group by on the feed types.

here are the tables for clarity:

create table supplier
(supplier_id number(2) NOT NULL,
supplier_name varchar2(20),
supplier_add varchar2(55),
supplier_postcode varchar2(8),
supplier_tel varchar2(15),
supplier_mob varchar2(15),
supplier_code varchar2(2),
foreign key (supplier_code) references supplier_type(supplier_code),
primary key (supplier_id))
/

create table livery_horse
(liv_horse_id number(3) NOT NULL,
horse_name varchar2(18),
breed varchar2(22),
colour varchar2(18),
sex varchar2(8),
d_o_b date,
freezemark varchar2(5),
livery_id number(1),
bedding varchar2(15),
status varchar2(18),
vet_id number(2),
groom_id number(2),
stable_no number(3) NOT NULL,
foreign key (livery_id) references livery_type(livery_id),
foreign key (vet_id) references vet(vet_id),
foreign key (groom_id) references employee(employee_id),
foreign key (stable_no) references stable(stable_no),
primary key (liv_horse_id))
/

create table riding_horse
(rid_horse_id number(3) NOT NULL,
horse_name varchar2(18),
height number(3,1),
breed varchar2(22),
colour varchar2(18),
sex varchar2(8),
d_o_b date,
freezemark varchar2(5),
weight_limit number(2),
work_limit number(3),
temperament varchar2(9),
vaccination_due date,
groom_id number(2),
foreign key (groom_id) references employee(employee_id),
primary key (rid_horse_id))
/

create table feed
(feed_id number(3) NOT NULL,
feed_name varchar2(25) NOT NULL,
feed_mfr_name varchar2(25),
supplier_id number(2),
foreign key (supplier_id) references supplier(supplier_id),
primary key (feed_id))
/

create table riding_horse_meal
(rid_horse_id number(3),
feed_id number(3) NOT NULL,
feed_qty number(3,1),
feed_time varchar2(4),
foreign key (rid_horse_id) references riding_horse(rid_horse_id),
foreign key (feed_id) references feed(feed_id),
primary key (rid_horse_id, feed_id, feed_time))
/

create table livery_horse_meal
(liv_horse_id number(3),
feed_id number(3) NOT NULL,
feed_qty number(3,1),
feed_time varchar2(4),
foreign key (liv_horse_id) references livery_horse(liv_horse_id),
foreign key (feed_id) references feed(feed_id),
primary key (liv_horse_id, feed_id, feed_time))
/

I would like the report to show Feed name, supplier_name, sum(feed_qty) - for both types of horses - i.e. total feed used.

any suggestions?



Start Free Trial