Re: beginner's question

Please look at this...
May be I don't understand youre question?

SQL> create table person(ID number primary key, Name varchar2(10)); Table created.

SQL> create table Donation(PersonID number,   2 Amount number,
  3 CharityID number);
Table created.

SQL> create table Volunteer(PersonID number,   2 Hours
  3 number,
  4 CharityID number);
Table created.

SQL> create table Charity(ID number primary key, Name varchar2(10)); Table created.

SQL> alter table Donation
  2 add constraint fk_don_person foreign key (PersonID) references Person(ID);
Table altered.

SQL> alter table Donation
  2 add constraint fk_don_charity foreign key (CharityID) references Charity(ID);
Table altered.

SQL> alter table Volunteer
  2 add constraint fk_vol_person foreign key (PersonID) references Person(ID);
Table altered.

SQL> alter table Volunteer
  2 add constraint fk_vol_charity foreign key (CharityID) references Charity(ID);
Table altered.

SQL> insert into person values(1,'person1'); 1 row created.

SQL> insert into person values(2,'person2'); 1 row created.

SQL> insert into person values(3,'person3'); 1 row created.

SQL> insert into charity values(1,'Charity1'); 1 row created.

SQL> insert into charity values(2,'Charity2'); 1 row created.

SQL> insert into Volunteer values(1,10,2); 1 row created.

SQL> insert into Volunteer values(3,30,1); 1 row created.

SQL> insert into Donation values(2,100,1); 1 row created.

SQL> insert into Donation values(3,300,2); 1 row created.

SQL> select p.Name, d.Amount, v.Hours, c.Name   2 from Person p, Donation d, Volunteer v, Charity c   3 where
  4 p.id = d.personID(+)
  5 and
  6 p.id = v.personID(+)
  7 and
  8 (v.charityID = c.ID or d.charityID = c.ID )   9 ;

NAME          AMOUNT     HOURS NAME
----------          ---------          ---------   ----------
person1                             10          Charity2
person2          100                           Charity1
person3          300            30          Charity1
person3          300            30          Charity2

Kyle Lahnakoski <kyle_at_arcavia.com> wrote in message news:3847A764.F7BA5BF8_at_arcavia.com...
>
> I would doubt a simple state GUI would be enough to indicate the query
> request uniquely. Suppose we have 4 tables:
>
> Person
> ID
> Name
>
> Donation
> PersonID
> Amount
> CharityID
>
> Volunteer
> PersonID
> Hours
> CharityID
>
> Charity
> ID
> Name
>
> By specifying the person name and the charity name, you will not have
> enough information for your query. The automation will not know if you
> are trying to find all volunteers or all donators.
>
> Maybe I got this wrong. Can you be more specific or use an example?
>
>
>
>
> Sandy Pittendrigh wrote:
> >
> > For any schema where any table can (somehow) be connected
> > to any other table through a connected path of
> > primary_key/foreign pairs, it must be possible for an
> > external parsing routine to read the data dictionary and
> > construct a connected, directional graph of the entire
> > schema.
> >
> > Given this, and also given an arbitrary collection of GUI
> > query screens, where each widget in the query screen can be
> > toggled on or off, and where each widget in the query screen
> > is associated with a (tablename, column_name, data_type)
> > touple, then it must be (is it?) possible for that external
> > parsing routine to read the "widget state" of the screen
> > and dynamically construct the "foreign_key=primary_key"
> > clauses needed to make a logical select statement.
> >
> > In other words, any query screen that interactively prompts
> > for the "SELECT columns" part of a select statement as well
> > as the "WHERE constraints" part of the select statement, the
> > only hard part left is to add in the "link this table to
> > that table" clauses. The programmer who designed the schema
> > can do this. But what about a parsing routine that only
> > knows the state of the query screen?
> >
> > --
> > /* Sandy Pittendrigh >--oO0>
> > * sandy_at_nervana.montana.edu
> > * http://nervana.montana.edu/~sandy
> > */
>
> --
> ----------------------------------------------------------------------
> Kyle Lahnakoski
> (416) 892-7784
> kyle_at_arcavia.com
Received on Fri Dec 03 1999 - 00:00:00 CET