Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland 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
Microsoft SQL ServerSSAS

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Phillip Burton

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.
mankowitz

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
Éric Moreau

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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Vitor Montalvão

I don't think I get what you want.
Can you explain with examples?
Murray Brown

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
Phillip Burton

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).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Murray Brown

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
Vitor Montalvão

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Murray Brown

ASKER
Thanks Vitor
Vitor Montalvão

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy