Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Use WITH RECURSIVE to Query Hierarchical Data

- 05.06.26 - ErcanOPAK

🌳 Org Charts, Categories, Comment Threads

Self-joins are messy. Recursive CTEs query parent-child relationships elegantly. Get entire organization tree in one query.

📝 Employee Hierarchy

WITH RECURSIVE org_tree AS (
  -- Anchor: top-level managers
  SELECT id, name, manager_id, 0 as level, name as path
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive: employees under each manager
  SELECT e.id, e.name, e.manager_id, ot.level + 1,
         ot.path || ' -> ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, 
       REPEAT('  ', level) || name as indented_name,
       level,
       path
FROM org_tree
ORDER BY path;

🎯 Category Breadcrumbs

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, name as breadcrumb
  FROM categories
  WHERE parent_id IS NULL
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id,
         ct.breadcrumb || ' > ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, breadcrumb
FROM category_tree
ORDER BY breadcrumb;

-- Results:
-- Electronics > Computers > Laptops
-- Electronics > Computers > Desktops
-- Electronics > Phones > Smartphones
-- Clothing > Men > Shirts

✅ Find All Subordinates

  • Get team under specific manager: WHERE id = 123
  • Limit recursion depth: WHERE level < 5
  • Count subordinates: SELECT manager, COUNT(*) GROUP BY manager_id
  • Detect cycles: Use cycle detection (PostgreSQL, SQL Server)

“Employee org chart was 7 levels deep. Self-joins were impossible. Recursive CTE did it in 20 lines. Now reports generate in 50ms instead of 5 seconds.”

— Data Engineer

Related posts:

SQL “High CPU for No Reason” — The Missing Statistics Update

SQL “High CPU for No Reason” — The Missing Statistics Update

SQL: Use JSON Functions to Query JSON Columns

SQL: Use JSON Functions to Query JSON Columns

SQL: PostgreSQL Window Functions ile Karmaşık Veri Analizi

SQL: PostgreSQL Window Functions ile Karmaşık Veri Analizi

Views: 9

Post navigation

.NET Core: Publish as Native AOT for Self-Contained Executables
C#: Use CallerArgumentExpression for Better Argument Validation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com