CODEWITHSUNDEEP
sqlsql-server
Bob Vandevliet
Bob Vandevliet

Reputation: 213

SQL Server : join same column multiple times

I'm trying to get and join information from a SQL Server from two tables.

Let's say I have these two tables:

Table Resources:

ID    Resource
---------------
1     Alex
2     Dennis
3     Diane

Table Projects:

Project      Manager    Leader    Engineer
------------------------------------------
pro_18001    1          2         3

I want to get the following result:

Project        Manager    Leader    Engineer
---------------------------------------------
pro_18001      Alex       Dennis    Diane

I suppose the SQL query should look something like:

SELECT
  [Projects].[Project]
  [Resources].[Resource]
  [Resources].[Resource]
  [Resources].[Resource]
FROM
  [Projects]
LEFT JOIN
  [Resources] ON
    [Projects].[Manager] = [Resources].[ID] AND
    [Projects].[Leader] = [Resources].[ID] AND
    [Projects].[Engineer] = [Resources].[ID]
WHERE
  [Project].[Projects] = 'pro_18001'

But I'm sure the below part is not right as there is 3 times the same column and the join might not pick the right ones in the right order...

SELECT
  [Projects].[Project]
  [Resources].[Resource]
  [Resources].[Resource]
  [Resources].[Resource]

So how to join the same column multiple times in the right place?

Upvotes: 0

Views: 3056

Answers (3)

Selvam Kuppuswamy
Selvam Kuppuswamy

Reputation: 39

SELECT
    p.project_name,
    r.name AS manager,
    r1.name AS leader,
    r2.name AS enginener
FROM
    projects p
INNER JOIN resources r ON
    p.manager = r.id
INNER JOIN resources r1 ON
    p.leader = r1.id
INNER JOIN resources r2 ON
    p.engineer = r2.id

Upvotes: 2

bat7
bat7

Reputation: 866

you need to add the left join 3 instead of 1 for each column:

SELECT
  [Projects].[Project],
  Manager.[Resource] AS Manager,
  Leader.[Resource] AS Leader,
  Engineer.[Resource] AS Engineer
FROM [Projects]
LEFT JOIN [Resources] Manager
       ON [Projects].[Manager] = Manager.[ID]
LEFT JOIN  [Resources] Leader
       ON [Projects].[Leader] = Leader.[ID]
LEFT JOIN [Resources] Engineer
       ON [Projects].[Engineer] = Engineer.[ID]
WHERE [Project].[Projects] = 'pro_18001'

Upvotes: 7

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can try the below query for your required result

create table #Resources (Id int, Resources Varchar(20))
insert into #Resources 
values(1, 'Alex'), (2, 'Dennis'), ('3', 'Diane')

Create table #Projects (Project Varchar(20), Manager INT, Leader INT, Engineer INT)
insert into #Projects Values ('pro_18001', 1, 2, 3)

SELECT #Projects.Project,Manager.Resources as Manager,Leader.Resources as Leader, Engineer.Resources as Engineer FROM #Projects
INNER JOIN #Resources as Manager on #Projects.Manager = Manager.Id
INNER JOIN #Resources as Leader on #Projects.Leader = Leader.Id
INNER JOIN #Resources as Engineer on #Projects.Engineer = Engineer.Id

DROP TABLE #Resources
DROP TABLE #Projects

The output is as follows

Project     Manager Leader  Engineer
pro_18001   Alex    Dennis  Diane

Hope this will help you.

Upvotes: 1

Related Questions