Subject | Generating the WHERE clause |
---|---|

Author | Andy Canfield |

Post date | 2007-01-30T04:04:02Z |

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. Can you give me a URL of something that will

help me?

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.

Thank you.

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. Can you give me a URL of something that will

help me?

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.

Thank you.