Looking for TSQL Query to display Parent child relationships

Question

Looking for TSQL Query to display Parent child relationships

vsamantha35

SSChampion

Points: 11529
More actions
July 2, 2018 at 3:25 am

#357056

Hi All,
Checking if anybody has query which displays all the user tables in a Tree format based on parent child relationship.
CREATE TABLE [dbo].[Dept](
    [DEP_ID] [int] NOT NULL PRIMARY KEY ,
    [DEP_Name] [nvarchar](50) NULL,
    [DEP_Location] [nvarchar](max) NULL
)
GO
CREATE TABLE [dbo].[EmploymentStatus](
[ID] [int] NOT NULL PRIMARY KEY ,
[EmpStatus] INT NULL,
)
GO
CREATE TABLE [dbo].[Employees](
    [EmpID] [int] NOT NULL PRIMARY KEY,
    [Emp_First_Name] [nvarchar](50) NULL,
    [Emp_Last_Name] [nvarchar](50) NULL,
    [EmpDepID] [int] NOT NULL CONSTRAINT [FK_DEP] FOREIGN KEY([EmpDepID]) REFERENCES [dbo].[Dept] ([DEP_ID]) ON DELETE CASCADE,
    [Emp_Status] [int] NOT NULL CONSTRAINT [FK_Stat] FOREIGN KEY([Emp_Status]) REFERENCES [dbo].[EmploymentStatus] ([ID]) ON DELETE CASCADE,
    [EMP_PhoneNumber] [nvarchar](50) NULL,
    [Emp_Adress] [nvarchar](max) NULL
)
GO
CREATE TABLE [dbo].[Salaries](
ID INT IDENTITY (1,1) PRIMARY KEY,
[Emp_ID] [int] NOT NULL CONSTRAINT [FK_Sal] FOREIGN KEY([Emp_ID]) REFERENCES [dbo].[Employees] ([EmpID]) ON DELETE CASCADE,
[EmpSalary] INT NULL,
[EmpBankAccount] VARCHAR(100)
)
GO
Expected Output
===============
DEPT
|_[Employees]
  |_ [Salaries]
[EmploymentStatus]
|_[Employees]
  |_ [Salaries]
Basically, I am looking for parent child relation ships in a tree format using TSQL.
Thanks,
Sam

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 1

If it's for a one-off task, you can use SQL Diagrams, which is a built in tool. It's fairly limited but for simple tasks such as you describe, it may fit the bill.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76944 More actions · Answer 2

The problem here is that databases aren't necessarily trees. The simplest example of this is the employee/manager relationship where the employee record has a link to their manager's employee record. In other words, the table contains a foreign key to a different field on the same table. There are also more complex relationships, and it's almost impossible to come up with a general solution, because of that.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA