Does anyone have code to generate on the fly a SQL FROM clause based on a list of user chosen tables?
I am creating a database for my boss that has a form that is an attempt to make a more user-friendly version of the Access Query Builder Environment. It is a simple multiple step form to help the user generate a query.
(Select tables and fields -> Adjust Joins if necessary (inner,right,left) -> Where clause -> etc.)
Obviously the functionality of my form is not as robust as the Access QBE but it is better than a form selecting from a preset query.
My problem, however, is figuring out an adequate algorithm for generating the user's FROM clause. My algorithm is adequate for really simple Parent->Child relationships in a hierarchical sequence but it doesn't work when I have say Parent1 -> Child1 and Parent2 -> Child1 (a tertiary relationship) or when one table is has a recursive relationship.
It seems to be a matter of getting the order right but the possiblities are more than I bargained for.
I could post my code but I'm thinking that I will have to start from scratch with a new algorithm.
Any help is greatly appreciated. Thanks.
I am creating a database for my boss that has a form that is an attempt to make a more user-friendly version of the Access Query Builder Environment. It is a simple multiple step form to help the user generate a query.
(Select tables and fields -> Adjust Joins if necessary (inner,right,left) -> Where clause -> etc.)
Obviously the functionality of my form is not as robust as the Access QBE but it is better than a form selecting from a preset query.
My problem, however, is figuring out an adequate algorithm for generating the user's FROM clause. My algorithm is adequate for really simple Parent->Child relationships in a hierarchical sequence but it doesn't work when I have say Parent1 -> Child1 and Parent2 -> Child1 (a tertiary relationship) or when one table is has a recursive relationship.
It seems to be a matter of getting the order right but the possiblities are more than I bargained for.
I could post my code but I'm thinking that I will have to start from scratch with a new algorithm.
Any help is greatly appreciated. Thanks.