SQL Server Problem connecting two instances of the same RDB table via an intermediary table

nkoocmlb 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(101)

I am migrating an MS Access database to MS SQL Server where I will maintain it using SQL Server Management Studio (SSMS).

The Access db has a relationship that connects two instances of the same table via an intermediary table as indicated by the following access relationship diagram.

This entity relationship enables the representation of the organization structure.

Access lets you create the relationship above using drag and drop and automatically added the suffix _1 to distinguish the added instance.

The SQL is:

SELECT [T-Organisation].[Organisation Unit Id], 
       [T-Organisation].[Organisation UnitName], 
       [T-Organisation_1].[Organisation Unit Id], 
       [T-Organisation_1].[Organisation UnitName]
FROM  ([T-Organisation] 
INNER JOIN [T-OrganisationSubsidiary] 
        ON [T-Organisation].[Organisation Unit Id] = [T-OrganisationSubsidiary].[Parent Organisation Unit]) 
INNER JOIN [T-Organisation] AS [T-Organisation_1] 
        ON [T-OrganisationSubsidiary].[Subsidiary Organisation Unit] = [T-Organisation_1].[Organisation Unit Id];

Unless I am missing something SSMS doesn’t permit me to add (drag and drop) copies of tables to the Relationship Diagram like Access does. Does this mean I need to create a physical copy of the T-Organisation table and manually name it e.g., T-Organisation_1 or is there a better solution?

sql-server

来源：https://stackoverflow.com/questions/77486035/problem-connecting-two-instances-of-the-same-rdb-table-via-an-intermediary-table

2条答案

按热度按时间

icomxhvb1#

J.D. has answered the question at DBA Stack Exchange.

赞(0）回复(0）举报 2023-11-16

pengsaosao2#

SQL Server expects you to write the SQL for this kind of things yourself, rather than via drag and drop tools, and the solution to creating this SQL is to give ALL instances of the table their own alias.

SELECT o0.[Organisation Unit Id], 
       o0.[Organisation UnitName], 
       o1.[Organisation Unit Id], 
       o1.[Organisation UnitName]
FROM  [T-Organisation] o0
INNER JOIN [T-OrganisationSubsidiary] os
        ON o0.[Organisation Unit Id] = os.[Parent Organisation Unit])
INNER JOIN [T-Organisation] o1
        ON os.[Subsidiary Organisation Unit] = o1.[Organisation Unit Id];

Otherwise, references to [T-Organization] in the SELECT clause are ambiguous, as it's still possible for them to refer to either instance of the table.

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Problem connecting two instances of the same RDB table via an intermediary table

2条答案

相关问题

热门标签

最新问答