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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
mankowitzCommented:
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
0
Éric MoreauSenior .Net ConsultantCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't think I get what you want.
Can you explain with examples?
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
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
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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).
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
murbro, what you want only works if the tables have foreign keys so you can determine their relationships.
You can start with this query:
SELECT * FROM sys.foreign_keys

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks Vitor
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.