Solved

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

Posted on 2014-11-11
10
96 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 69

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
 
LVL 45

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 45

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 45

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now