SQL Server : join same column multiple times

Question

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?

Isn't that 'pivot table' what you're trying to achieve: https://stackoverflow.com/questions/7674786/mysql-pivot-table — , Dec 03 '18 at 08:58
@ygorbunkov that's not pivoting. The results don' exist in either table. The `Resources` table has to return different rows for the same project. — Panagiotis Kanavos, Dec 03 '18 at 08:59
Thanks for the comments. @PanagiotisKanavos How do you mean "to join the tables multiple times"? I'm using MS SQL Server, just added the MySQL for better reach as the query is very similar. Maybe I should use "aliases" in some way? — Bob Vandevliet, Dec 03 '18 at 09:07
Indeed I just saw after a refresh. Thanks, I will try it in my project and let you know if it works ;) — Bob Vandevliet, Dec 03 '18 at 09:11
You'd better change your data structure because if you have to add new roles you will have to add columns to the Projects table, if you have more than one Engineer for a project you will have to duplicate other columns in new rows and so on. Too many problems... — Denis Rubashkin, Dec 03 '18 at 09:16
Yes I would do it differently as well, but it is an existing database for which I'm writing a new user interface ;) — Bob Vandevliet, Dec 03 '18 at 10:10

score 7 · Accepted Answer · edited Dec 03 '18 at 11:21

7

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'

edited Dec 03 '18 at 11:21

Bob Vandevliet

213
3
14

answered Dec 03 '18 at 08:59

bat7

836
1
8
22

Thanks, I just had to do `SELECT Manager.[Resource] AS Manager` instead of just `SELECT Manager.[Resource]` et cetera, to avoid a result having multiple columns with the exact same name. – Bob Vandevliet Dec 03 '18 at 09:46

score 2 · Answer 2 · edited Dec 05 '18 at 07:11

2

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

edited Dec 05 '18 at 07:11

Suraj Kumar

5,547
8
20
42

answered Dec 03 '18 at 09:33

Selvam Kuppuswamy

39
4

1

Please edit your post and explain why/how your code fixes the issues. As it stands "code only" answers are considered low quality. It will also improve the chances of the Author learning from their mistake. – DarkMukke Dec 05 '18 at 10:04

score 1 · Answer 3 · answered Dec 03 '18 at 09:09

1

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.

answered Dec 03 '18 at 09:09

Suraj Kumar

5,547
8
20
42

2

I suspect the OP *wants* the LEFT JOINs in case one of the resources doesn't exist. As it is, this query doesn't filter by the project's ID – Panagiotis Kanavos Dec 03 '18 at 09:10
Left join also will give the same result at present and the name will be empty or null when the data will not be present and ISNULL can be used there. – Suraj Kumar Dec 03 '18 at 09:15
1

@PanagiotisKanavos This should definitely give the OP the right idea on how to apply what Suraj gave as an answer. If OP wanted working code all the time to fix their bugs, they can go to fiver? This answer is good enough to help anyone with same question – zuckerburg Dec 03 '18 at 09:16
@zuckerburg - Thanks for your valuable feedback. – Suraj Kumar Dec 03 '18 at 10:47

SQL Server : join same column multiple times

3 Answers3