At 03:04 PM 30/01/2007, you wrote:

>I'm trying to create an inquiry tool that is simple enough for managers
>to use. Through check boxes I know what tables and fields he wants to
>see, sums, averages, etc. From this I can construct the GROUP BY clause
>and the ORDER BY clause. I have structured DDL.
>
>I do not have the algorithm to construct the WHERE clause that ties
>multiple tables together. It seems to involve an abstract network, with
>nodes=tables and directedlinks=foreignkeys. Perhaps a path, perhaps a
>spanning tree, is needed. The difficulty can be seen when the tables
>are not directly linked.
>
>For example, the SQL statement might look like this:
> SELECT STATE.NAME, SUM( EMPLOYEE.INCOME )
> FROM ...
> WHERE ...
> GROUP BY STATE.NAME
> ORDER BY STATE.NAME
>
>For a given DDL the WHERE clause might have to be
> WHERE EMPLOYEE.NAME = PERSON.NAME
> AND PERSON.STATECODE = STATE.STATECODE
>
>In this example, the tables EMPLOYEE and STATE are not directly linked
>together, but can be tied together through an intermediate table
>PERSON. Given the SELECT clause, how to find the intermediate table(s)
>through which the connections must be made? I can come up with rules
>that *seem* to work, but I do not understand the theory well enough to
>have confidance in my technique.

Well, for a start, don't use WHERE clauses to specify JOIN
criteria. That is very ancient SQL that has been deprecated since
about 1992! Use explicit JOIN syntax and use your WHERE clause only
for your search predicates, which you should parameterise.

Secondly, try to understand parameterised queries.

Thirdly, review your concept of ad hoc queries to separate the
*structure* of the set from the values of the search criteria. By
this means, your user can decide separately the data he wants to
retrieve and how to order and aggregate it (structure) and the
criteria for selecting it (search predicates).

Fourthly, and relevant to your example here, is the need to
understand the rules for aggregation.

With these in your armory, your technique for building the queries
becomes mechanical and (compared to your totally unstructured
approach) simple.

The structural side of a query specification is static; the
selection criteria are defined structurally but parameterising them
makes them dynamically. You can repeatedly run the same query with
different values in the search parameters without the need to rebuild
the query or re-prepare it.

Here's what your example might look like, once you are ready to
prepare it, and assuming that you're just looking for a total by STATE:

SELECT
STATE.NAME,
SUM( EMPLOYEE.INCOME )
FROM state
JOIN PERSON
on person.statecode = state.statecode
JOIN EMPOYEE
on employee.name = person.name
WHERE <search clause to restrict the rows considered>
GROUP BY STATE.NAME
ORDER BY STATE.NAME

For a given DDL the WHERE clause might have to be
WHERE EMPLOYEE.NAME = PERSON.NAME
AND PERSON.STATECODE = STATE.STATECODE

(although one would certainly hope the user had better normalisation
to work with in a real database!!)

>Can you give me a URL of something that will help me?

A t.y. book on standard SQL and retrieval algorithms? such as "SQL
Puzzles" (I think it's by Joe Celko). Or just Google for "SQL
algorithms" and you might well come across some useful tutorials on-line.

./heLen