CODEWITHSUNDEEP
mysql
AltSid
AltSid

Reputation: 43

SQL Joins Performance Considerations

I am looking for better way to achieve Result 2 that I have achieved using Query 2. In simple words, I am trying to merge File and CD columns into a single row for each SalesDetailId. It's obvious that Query 2 is not ideal for performance. Query 1 and Result 1 is what I started with which shows the kind of data structure I am working with.

Query 1:

SELECT 
  SDT.SalesDetailId,
  RlFile.Code AS FILE,
  RlCD.Code AS CD
FROM SalesDetail SDT
INNER JOIN Sales S
  ON S.SalesID = SDT.SalesID
LEFT JOIN SalesReflookup SRL 
    ON SRL.SalesID = S.SalesID AND SRL.RefLookupTypeId IN (30,200)  
LEFT JOIN reflookup.reflookup RlFileStatus 
    ON RlFileStatus.reflookupid = SRL.reflookupid AND RlFile.reflookuptypeid = 30
LEFT JOIN reflookup.reflookup RlCatastrophe 
    ON RlCD.reflookupid = SRL.reflookupid AND RlCatastrophe.reflookuptypeid = 200
WHERE 
    SDT.SalesDetailId = 4042910

Result 1:

| SalesDetailId | FILE |  CD  |
|       4042910 | C    | NULL |
|       4042910 | NULL | 95E  |

Query 2:

SELECT 
  SDT.SalesDetailId,
  FILE.Code AS FILE,
  CD.Code AS CD
FROM SalesDetail SDT
INNER JOIN Sales S
  ON S.SalesID = SDT.SalesID
LEFT JOIN (SELECT
          RL.code,SRL.SalesID
          FROM reflookup.reflookup RL
          INNER JOIN SalesReflookup SRL 
            ON RL.reflookupid = SRL.reflookupid AND RL.reflookuptypeid = 30) FILE ON FILE.SalesID = S.SalesID
LEFT JOIN (SELECT
          RL.code,SRL.SalesID
          FROM reflookup.reflookup RL
          INNER JOIN SalesReflookup SRL 
            ON RL.reflookupid = SRL.reflookupid AND RL.reflookuptypeid = 200) CD ON CD.SalesID = S.SalesID

WHERE SDT.SalesDetailId = 4042910

Result 2 (Desired Result)

| SalesDetailId | FILE | CD |
|       4042910 | C           | 95E            |

Upvotes: 2

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use GROUP BY on the first query:

SELECT SDT.SalesDetailId,
       MAX(RlFile.Code) AS FILE,
       MAX(RlCD.Code) AS CD
FROM . . .
WHERE  SDT.SalesDetailId = 4042910
GROPU BY SDT.SalesDetailId;

Upvotes: 2

Related Questions