Скачать или смотреть How to Retrieve Connected Rows in SQL Server with Ease

A comprehensive guide on using recursive CTEs (Common Table Expressions) in SQL Server to retrieve all connected AccountIds from hierarchical data using a single AccountId.
---
This video is based on the question https://stackoverflow.com/q/63920163/ asked by the user 'Ben.S' ( https://stackoverflow.com/u/11522395/ ) and on the answer https://stackoverflow.com/a/63920800/ provided by the user 'Thom A' ( https://stackoverflow.com/u/2029983/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Retrieve connected rows in SQL Server

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the Problem: Retrieving Connected Rows in SQL Server

When working with relational databases, you may encounter scenarios where you need to retrieve connected rows based on relationships defined in your data. This is especially common when dealing with hierarchical or interconnected datasets. In this post, we'll focus on a specific example using SQL Server to demonstrate how to effectively retrieve connected AccountIds from a given set.

The Dataset

Consider a table with the following structure:

AccountIdAccountIdAssociated233151303121210105019321833Scenario

Suppose you want to find all connected AccountIds starting from a specific AccountId (for example, AccountId = 2). This means not only the directly associated accounts but also any accounts that are indirectly connected through other accounts.

The Solution: Using Recursive CTEs

To achieve this, we can utilize recursive Common Table Expressions (CTEs) in SQL Server. CTEs allow us to perform recursive queries, which is ideal for traversing hierarchical data.

Steps to Retrieve Connected Rows

Define the Base CTE: Start with a base CTE that incorporates your dataset. In our case, we'll create a CTE named VTE that contains account relationships.

Set Up Recursive CTEs: Create two recursive CTEs:

One for traversing upwards (rCTEUp) to find accounts directly linked to others.

Another for traversing downwards (rCTEDown) to find accounts that are associated with the starting account.

Combine Results: Finally, combine results from both CTEs to get a complete list of connected accounts.

Example SQL Code

Here's the SQL code to accomplish the task:

[[See Video to Reveal this Text or Code Snippet]]

Step-by-Step Breakdown

Step 1: Base Data Definition
We use the WITH clause to create the base CTE VTE, which mimics our original dataset for internal processing.

Step 2: Traversing Upwards and Downwards

rCTEUp: This part starts with an initial AccountId and finds all accounts associated upward.

rCTEDown: Similarly, this CTE starts with an AccountId and looks for all accounts associated downward.

Step 3: Combining Results
Finally, we select all results from both upward and downward CTEs, ensuring no duplicates with the help of the UNION ALL clause.

Conclusion

By leveraging recursive CTEs in SQL Server, you can efficiently retrieve all connected AccountIds by starting from a single account number. This method is powerful for exploring relationships in hierarchical data and can be adapted to various structures and scenarios in your database.

With this approach, you not only retrieve direct connections but also explore transitive relationships, providing a holistic view of connected accounts. Happy querying!