Ian Ian - 7 months ago 22
SQL Question

Selecting Same Column Twice Using Alias Table

Below are examples of the tables I am working with. These only represent the columns relevant to my query

_Requirements

RequirementID fkOwningWsID
-------------------------------------------
REQ-RPT-01 1
REQ-RPT-02 2


_Workstream

pk WsNm
-------------------------------------------
1 Workstream1
2 Workstream2


mnWorkstream_Leads

fkWsID fkEeID
-------------------------------------------
1 1
1 2
2 1
2 2


The below table is a result of a union. Employees can be from different companies, the below union lists all the employee IDs, the IDs for the employees who are from Company 1 (0 otherwise) and IDs for employees from company 2 (0 otherwise)

qryTrackerAllEeList

EeID Company1_ID Company2_ID
-------------------------------------------
1 1 0
2 0 2


I am attempting to view the following result

RequirementID WsNm Company1_Lead Company2_Lead
--------------------------------------------------------------------
REQ-RPT-01 Workstream1 1 2
REQ-RPT-02 Workstream2 1 2


I have issued the following SQL

SELECT DISTINCT Req.RequirementID, Ws.Wsnm, company1_id.ee_id, company2_id.ee_id
FROM (((([_Requirements] AS Req
INNER JOIN [_Workstream] AS Ws ON Req.fkOwningWsID = Ws.pkWsID)
INNER JOIN [mnWorkstream_Leads] AS wsLeads ON Ws.pkWsID = wsLeads.fkWsID)
LEFT OUTER JOIN qryTrackerAllEeList AS company1 ON wsLeads.fkEeID = company1.Company1_ID)
LEFT OUTER JOIN qryTrackerAllEeList AS company2 ON wsLeads.fkEeID = company2.Company2_ID)


The issue is, however, that I retrieve the following results

RequirementID WsNm Company1_Lead Company2_Lead
--------------------------------------------------------------------
REQ-RPT-01 Workstream1 2
REQ-RPT-01 Workstream1 1
REQ-RPT-02 Workstream2 2
REQ-RPT-02 Workstream2 1


Any suggestions on how to eliminate these duplicative rows and null values?

Answer

Use MAX() and GROUP BY to only select the non null values and group them into one row:

SELECT DISTINCT Req.RequirementID,  Ws.Wsnm, 
MAX(company1_id.ee_id) as Company1_Lead, MAX(company2_id.ee_id) as Company2_Lead,
FROM (((([_Requirements] AS Req 
INNER JOIN [_Workstream] AS Ws ON Req.fkOwningWsID = Ws.pkWsID)
INNER JOIN [mnWorkstream_Leads] AS wsLeads ON Ws.pkWsID = wsLeads.fkWsID)
LEFT OUTER JOIN qryTrackerAllEeList AS company1 ON wsLeads.fkEeID = company1.Company1_ID)
LEFT OUTER JOIN qryTrackerAllEeList AS company2 ON wsLeads.fkEeID = company2.Company2_ID)
GROUP BY req.RequirementID, Ws.Wsnm