Need Code to Link Tables into a Query

[JDYoder]

We have an issue that I'm hoping some guru can help me with. We have a SQL DB and we allow users to select various fields from various tables. What I need to do is dynamically create a query that joins the selected tables together, via the relationhips in the DB.

 

The code to do this would be quite extensive, but elegant. I know relationships are stored in a SQL table (not sure which one) but I was hoping someone would know of such code that can take Table1 and Table2, and link them together via "the path of least resistance" for the query.

 

For example, if the user picks fields from tables "Customer" and from "Order Detail," the code would determine the SQL statement that links "Customers" to "Order Header" and "Order Header" to "Order Detail".

[Nerseus]

Check out the tables sysreferences or sysforeignkeys and sysobjects. sysobjects will get you the table name from an id. You'll have to do some digging to see which of the other sys* tables you need.

 

To do this, you'd have to make some assumptions. For example, do you do inner or outer joins? If you do inner, you assume there's always at least one child row in every table.

 

It might be easier to create your own metadata table(s) to handle these complex, dynamic joins. You could map tables there manually along with relationship info (inner, outer, etc.). I would guess this might be better since (I hope) you're limiting the tables and columns that can be selected from (reports?) and thus your metadata table won't be that big.

 

-ner

[JDYoder]

>> It might be easier to create your own metadata table(s) to handle these complex, dynamic joins...

 

We were wondering about that. And maybe we'll have to do something like that. However, we'd like to at least be able to play with the idea of joining tables thru other tables that are unknown at the moment.