Re: Questions on specifying table relationships

On Thursday 31 Oct 2002 12:36 am, Patrick Bakker wrote:
[long description of automatic query generator]
> I'm not entirely clear what I'm asking here but I think it comes down to
> confirming the following:
>  (Q) What do I need to autogenerate the relationships in a query?
>  (A)
>     - tables used in the query
>     - fields connecting the tables together
>     - what type of join exists between each table

And I fear, the semantics of each possible join. Without knowing what it means
to connect two tables via two columns I'm not sure the automated system could
decide between options.

>  (Q) Since the user can choose fields from tables which are not directly
> connected, what is the best way to
>      determine a relationship/join path to connect the tables? ie. which
> items did this customer buy?
>      would require item -> order_line -> order -> customer given only item
> and customer ...
>
>     - something is missing here: because item -> purchase_line ->
> purchase_order -> customer could also
>     match (unless purchase_order people are listed in vendor instead of
> customer ...)

I think it's the meaning that's missing. I don't have any clever suggestions
here as to how to model the semantics a query.

Could you get away with a set of query templates and then select which fields?

e.g.
choice 1 "show items purchased per customer"
choice 2 "list customer id,name item name, quantity, price"
choice 3 "for Jan 2002, sorted by cutomer id"

Choice 1 would be to pick a template and would determine both the join and
what fields were available for subsequent choices.

> Sorry for the brain dump but I would appreciate it somebody can clarify
> anything.
> Patrick

Sorry I've only addressed a couple of your questions. Hope my mutterings have
been of some use.

--
  Richard Huxton