CODEWITHSUNDEEP
sql-servert-sqlselectsql-server-2012
ratanmalko
ratanmalko

Reputation: 471

TSQL - Select column values from one table to another one

In a select statement (MSSQL 2012) I want to add column values from one table to another one.

It's best explained by this picture: enter image description here

I want to add the text Manager from the first table to the Role of the second already joined table including the names of the Manager column.

As a result there would be two more rows (DeptID 1 and DeptID 2) with role name "Manager" and the "User A" and "User B" in the Member column.

Just like the Role name should always be "Manager" the SubTeamName should always be "Core".

Sample data:

CREATE TABLE Base
    (DeptID int, [Manager] varchar(13))
;

INSERT INTO Base
    (DeptID,[Manager])
VALUES
    (1,'User A'),
    (2,'User B'),
    (3,'User C')
;

CREATE TABLE TeamMember
    (ID int,DeptID int,RoleID int, [Member] varchar(57) )
;
INSERT INTO TeamMember
    ([ID],DeptID, RoleID, Member)
VALUES
    (44, 1,2, 'User D'),
    (55, 2,3, 'User E')
;

CREATE TABLE SubTeam
    (ID int, [SubTeamName] varchar(57) )
;

INSERT INTO SubTeam
    ([ID],[SubTeamName])
VALUES
    (44,  'Core'),
    (55,  'Extended')
;

CREATE TABLE Role
    (ID int, SubID int,  [Role] varchar(57))
;
INSERT INTO Role
    (ID, SubID, [Role])
VALUES
    (1,44, 'Sales'),
    (2,44, 'Finance'),
    (3,55,  'Development')
;

Current query is:

SELECT b.DeptID, t.RoleID, r.Role, t.Member, s.SubTeamName 
FROM SubTeam s
LEFT JOIN Role r ON s.ID = r.SubID
LEFT JOIN TeamMember t ON r.ID = t.RoleID
LEFT JOIN Base b ON t.DeptID = b.DeptID
WHERE b.DeptID is not Null

Please also see this Fiddle

Maybe I'm thinking too complicated as I tried with Union statement but I believe there is an easier way I hope you can show me.

Upvotes: 2

Views: 61

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

A union is probably the easiest solution. I've noticed that your current query is a bit too cumbersome - you are using left joins but effectively changing them to inner joins with the condition in your where clause. Also, you are joining the Base table but not using it anywhere significant - so I've made a small change to your query.

This is what I came up with:

SELECT t.DeptID, t.RoleID, r.Role, t.Member, s.SubTeamName 
FROM SubTeam s
JOIN Role r ON s.ID = r.SubID
JOIN TeamMember t ON r.ID = t.RoleID

UNION 

SELECT b.DeptId, NULL, 'Manager', [Manager], 'Core'
FROM Base As b 
JOIN TeamMember As t ON b.DeptId = t.DeptId

See fiddle demo.

Upvotes: 1

Related Questions