CODEWITHSUNDEEP
sqldatabasems-accessrelational-database
Ian
Ian

Reputation: 107

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?

Upvotes: 1

Views: 503

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

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

Upvotes: 2

Related Questions