?
Solved

SQL loading table joins that will automatically be used by a query

Posted on 2014-11-11
10
Medium Priority
?
119 Views
Last Modified: 2016-02-18
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
0
Comment
Question by:Murray Brown
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434696
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
 
LVL 24

Expert Comment

by:mankowitz
ID: 40434701
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40434828
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40434842
I don't think I get what you want.
Can you explain with examples?
0
 

Author Comment

by:Murray Brown
ID: 40435697
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40436176
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
 

Author Comment

by:Murray Brown
ID: 40436650
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
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40436799
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
 

Author Closing Comment

by:Murray Brown
ID: 40436802
Thanks Vitor
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40436810
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question