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