CODEWITHSUNDEEP
sql-servert-sql
user1452574
user1452574

Reputation: 485

How do you join a table twice, with each join having its own alias?

So I have the code below, trying to join NonConformance table to EmplCode table twice, each with a different column withing NonConformance table and it's own alias, so I can get 2 different EmplName fields, 1 where EmplCode = EmplCode (alias a) and 1 where EmplCode = ResponsibleMgr (alias b). My code below doesn't work, there's an error in the Subquery and I can't figure it out. Thanks in advance 

SELECT
  CONVERT(DATE, a.NonConfDate) AS [NonConformance Date],
  a.ReturnType,
  a.LotNo AS [WorkCenter],
  LEFT(a.PartNo, 5) AS [OrderNo],
  a.PartNo,
  a.CustCode,
  a.VendCode,
  a.EmplName,
  a.PartDesc AS [Description],
  a.Disposition,
  b.EmplName,
  a.ProcessDate AS [DateResolved]
FROM
  ((SELECT * FROM NonConformance n LEFT JOIN EmplCode e ON n.EmplCode = 
  e.EmplCode) a 
  LEFT JOIN (SELECT * FROM NonConformance n LEFT JOIN EmplCode e ON 
  n.ResponsibleMgr = e.EmplCode) b)
ORDER BY a.NonConfDate DESC

Upvotes: 0

Views: 35

Answers (1)

Amit Kumar Singh
Amit Kumar Singh

Reputation: 4475

The error in your code is not joining 2 tables a and b with a ON clause. Remove outer parentheses also as mentioned in a comment above. You can use same field of NonConformance table and join as a.id=b.id.

Did you try using tables directly instead of subqueries. Check the possibility of using this as FROM clause.

    FROM NonConformance n 
LEFT JOIN EmplCode e 
ON n.EmplCode = e.EmplCode 
LEFT JOIN EmplCode x 
ON n.ResponsibleMgr = x.EmplCode 
ORDER BY n.NonConfDate DESC

Upvotes: 4

Related Questions