SQL Server : join same column multiple times

sql server join same table multiple times performance
joining the same table twice on different columns
joining the same table twice on same columns
avoid joining same table multiple times
sql join same table different columns
sql left join same column twice
multiple outer joins on same table
sql multiple joins

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?

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'

How to join same column twice?, 2 Answers. You need to use aliases on the tables. This is generally a good idea, and it is necessary when you have the same table multiple times in the from : SELECT t. 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

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.

How to join to the same table multiple times?, The hierarchy_table has 5 columns that all reference the name_table , so you need 5 joins. It may be better to use LEFT joins instead of INNER , in case some of  Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on a SQL forum. >> I have a transaction record [sic: rows are not records] which references a look-up table three times. << CREATE TABLE Transactions (trans_id INTEGER NOT NULL PRIMARY KEY,

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

How to join the same table twice in a single SQL query statement , How do I join the same table multiple times in SQL? One way to get all four player names in the same row (similar to eventDraw but with names) is to JOIN 4 instances of Member table: SELECT m1.LastName as LN1 ,m1.FirstName as FN1 ,m2.LastName as LN2 ,m2.FirstName as FN2 ,m3.LastName as LN3 ,m3.FirstName as FN3 ,m4.LastName as LN4 ,m4.FirstName as FN4 FROM EventDraw ed INNER JOIN Member m1 ON m1.MemberId = ed.PlayerOneID INNER JOIN Member m2 ON

An Illustrated Guide to Multiple Join, join types we must carefully consider the join sequence in order to produce the desired result. Rolling up data from multiple rows into a single row may be necessary for concatenating data, reporting, exchanging data between systems and more. This can be accomplished by: The solution proposed in this tip explores two SQL Server commands that can help us achieve the expected results. The SQL Server T-SQL commands used are STUFF and FOR XML.

Joining three or more tables in SQL, These columns are the employee IDs for the sales person, the department manager and the store manager. use MSSQLTips go create table dbo. If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance.

How to Join to the Same Table Multiple Times for a SQL Server query, sql left join same column twice multiple outer joins on same table sql multiple joins. I'm trying to get and join information from a SQL Server from two tables. A multiple join is a use of more than one join in a single query. The joins used may be all of the same type, or their types can differ. We’ll begin our discussion by showing an example query that uses two joins of the same type. Take a look at the query below.

Comments
  • You have to join the tables multiple times
  • Isn't that 'pivot table' what you're trying to achieve: stackoverflow.com/questions/7674786/mysql-pivot-table
  • @ygorbunkov that's not pivoting. The results don' exist in either table. The Resources table has to return different rows for the same project.
  • Are you using MySQL or MS SQL Server?
  • @BobVandevliet bat7 already posted this as an answer
  • 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.
  • 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
  • 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.
  • @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 - Thanks for your valuable feedback.
  • 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.