Murray Brown
asked on
SQL loading table joins that will automatically be used by a query
Hi
Is it possible to load table joins/relationships into a SQL database that will automatically be used by a query without having to specify the joins in that query
Is it possible to load table joins/relationships into a SQL database that will automatically be used by a query without having to specify the joins in that query
Not in SQL Server T-SQL; you can do that in SQL Server using MDX or DAX (using SSAS) or PowerPivot, if you build a cube or tabular model, you can define default relationships that the tables should use, and don't need to redefine them in your query.
perhaps you are looking for a VIEW?
CREATE VIEW custorders AS
SELECT * FROM Customers c JOIN Orders o on (o.account=c.account)
and then
SELECT * FROM custorders
see http://msdn.microsoft.com/en-us/library/ms187956.aspx
CREATE VIEW custorders AS
SELECT * FROM Customers c JOIN Orders o on (o.account=c.account)
and then
SELECT * FROM custorders
see http://msdn.microsoft.com/en-us/library/ms187956.aspx
Views are a good idea but I prefer User Defined Functions (UDF) that returns a table. You can pass them parameters. check http://msdn.microsoft.com/en-us/library/ms186755.aspx
I don't think I get what you want.
Can you explain with examples?
Can you explain with examples?
ASKER
Hi Vitor
I posed the question because I am trying to build the FROM part of a SQL statement where information on any number of joins is passed into a function:
So any number of joins passed into the following function where a join might be a comma delimited string such as "Table1.Column2,Right Join,Table6.Column1"
Function Build_From (ByVal arrJoins as string) as String
'Code here builds FROM part of SQL Statemenr
End Function
I posed the question because I am trying to build the FROM part of a SQL statement where information on any number of joins is passed into a function:
So any number of joins passed into the following function where a join might be a comma delimited string such as "Table1.Column2,Right Join,Table6.Column1"
Function Build_From (ByVal arrJoins as string) as String
'Code here builds FROM part of SQL Statemenr
End Function
Trouble is, there is not one unique relationship which can define the relationship between two tables.
Take the case of 2 tables, an order table and a date table. The relationship could be between order date and the date table; or it could be between shipping date and the date table; or between the received date and the date table.
There isn't necessarily a unique relationship. If you want to so define one, you'd be better off using PowerPivot or SSAS (perhaps in Tabular mode).
Take the case of 2 tables, an order table and a date table. The relationship could be between order date and the date table; or it could be between shipping date and the date table; or between the received date and the date table.
There isn't necessarily a unique relationship. If you want to so define one, you'd be better off using PowerPivot or SSAS (perhaps in Tabular mode).
ASKER
Thanks Philip but I have been building an Excel Add-in for three years that requires users to define joins using a simple drag and drop GUI. The drag and drop GUI in Access is a good example of what I would like to build. For now my System.Drawing skills are not quite there so I wanted to find a simple way to do this
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Vitor
Worked a little bit more on this and I think this is the query that you are searching for:
SELECT k.name AS FK_name, OBJECT_NAME(k.parent_object_id) TableName, pc.name ParentColumnName,
OBJECT_NAME(k.referenced_object_id) ReferenceTable, pc.name ReferenceColumnName
FROM sys.foreign_keys k
INNER JOIN sys.foreign_key_columns c
INNER JOIN sys.columns pc ON c.parent_column_id = pc.column_id AND c.parent_object_id = pc.object_id
INNER JOIN sys.columns rc ON c.parent_column_id = rc.column_id AND c.parent_object_id = rc.object_id
ON c.constraint_object_id = k.object_id
ORDER BY 2, 3