Re: beginner's question

You have shown there is a third way of interpreting the query!

I guess the original poster could be imagining your method: using OR, using additional filters to reduce that, but there could be problems. In this example there are only two paths to join the tables. But there could be designs with too many possible paths to be tractable. For any directed graph, without cycles, the number of paths can be O(e^n), where e is the natural e, and n is the number of edges. For this reason I doubt using OR was what the original poster thought.

I had always thought of designing a query visually as having to specify the path explicitly. My original idea was to start with the Start Table, then pick a column to join on. With a right click you get a list of all associated tables. Pick one, and the Start Table is replaced with the join of the two. Continue with this process until you reach the End Table.

Now that I see I was only specifying the path, I could imagine a host of other possible interfaces. For one, imagine the tables and their relationships all displayed on the screen. You could point and click the path for the query.

Andrey Edemsky wrote:
>
> 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
 

-- 
----------------------------------------------------------------------
                                                       Kyle Lahnakoski
                                                        (416) 892-7784
                                                      kyle_at_arcavia.com