Questions on specifying table relationships

From: Patrick Bakker <patrick(at)vanbelle(dot)com>
To: "PostgreSQL General (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: Questions on specifying table relationships
Date: 2002-10-31 00:36:13
Message-ID: A9CE1D556F89DD4FBA4CF797215DF61A02F364@20svbl1.vanbelle.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all.
My question follows eventually but requires some explanation. I'm not
entirely sure which list this should go to so general seems appropriate.

Suppose I want to find out which items a customer purchased from which
orders using the following tables:
customer, order, order_line, item.

When making joins for a query I've always written them as:

FROM
(item INNER JOIN
(order_line INNER JOIN
(order INNER JOIN
customer
ON order.fk_customer = customer.pk_customer)
ON order_line.fk_order = order.pk_order)
ON item.pk_item = order_line.fk_item)

Currently, I'm in the middle of an Java/EJB-style application where I'd like
to autogenerate queries. The user constructs a query by choosing options
regarding which fields they want to show, how they want to sort them, how
they want to group them, and how they want to limit the selection.
Essentially all of the basics of an SQL query. However, they only see the
fields using nice names and depending on their choices the query may result
in varying joins.

So if I follow my hand-coding practise I need to arrange the tables in an
order so that the joins are always 1 table apart. This feels unnecessary
somehow. Also, I sometimes get confused exactly how to write queries when a
single table requires multiple joins and they're not all necessarily INNER
JOINs. For example, suppose the item table also links to a UPC table and a
price table but the item may not have a UPC or a price:

FROM
(item_upc RIGHT JOIN
(item INNER JOIN
(item_price LEFT JOIN
(order_line ...

I'm fairly sure I've done this before but I feel like I should order the
joins so that the actual table relationships are 1 line apart. ie. the item
INNER JOIN should always be immediately before order_line ... Do I
understand it correctly that order_line will effectively be 'inner joined'
with the entire "meta upc-item-price table" in this case?

But back to the automatic query generation (I'm having trouble finding the
words to say exactly what my question is - perhaps somebody can suggest what
my question is if they can see what I'm getting at but not quite saying
...) - I'd like to avoid trying to automatically generate this series of
joins given only a list of tables and the fields which join the tables
together. My thought was to just dump these down into the WHERE clause of
the query but in that case won't I loose all ability to do anything but an
INNER JOIN? Also, is some method of specifying joins more efficient or
better suited to PostgreSQL?

Having said that, now I'm also wondering how I would know what type of join
should exist between tables (ie. when I manually form the query I know an
item doesn't always have a UPC).

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

(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 ...)

(Q) What's the best way of writing the join in PostgreSQL for this purpose?

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Justin Clift 2002-10-31 00:57:52 Re: French version of the PostgreSQL "Advocacy and
Previous Message Robert Treat 2002-10-31 00:21:58 Re: [ADMIN] Fwd: [BUGS] Fwd: FATAL 1: Database dialup does not exist in pg_database