Solved

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

Posted on 2014-11-11
10
104 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:murbro
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

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:murbro
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:murbro
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 49

Accepted Solution

by:
Vitor Montalvão earned 500 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:murbro
ID: 40436802
Thanks Vitor
0
 
LVL 49

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to keep a record with the highest value 3 39
Need SSIS project 2 27
SQL Query Returns Records in SSMS but not Classic ASP 5 21
SQL profiler 3 9
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

756 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