T-SQL Removing multiple LEFT JOIN

0
votes

I have such query. It returns ColA and ColB from TableA and UserName from table Users. Then it displays several fields from TableB as additional columns to results. It works but is there any better way than using these multiple LEFT JOINS ?

SELECT a.COlA, a.ColB, u.UserName,
b1.Value,
b2.Value,
b3.Value,
b4.Value,

FROM TableA a JOIN Users u ON a.UserId = u.UserId
    LEFT JOIN TableB b1 ON a.EventId = b1.EventId AND b1.Code = 5
    LEFT JOIN TableB b2 ON a.EventId = b2.EventId AND b2.Code = 15
    LEFT JOIN TableB b3 ON a.EventId = b3.EventId AND b3.Code = 18
    LEFT JOIN TableB b4 ON a.EventId = b4.EventId AND b4.Code = 40

WHERE (a.UserId = 3) ORDER BY u.UserName ASC

TableB looks like:

Id | EventId | Code | Value
----------------------------
1  |    1    |  5   | textA
2  |    1    |  15  | textB
3  |    1    |  18  | textC

Sometimes Code is missing but for each event there are no duplicated Codes (so each LEFT JOIN is just another cell in the same result record).

2
sqlsql-servertsqlleft-join
Does it work? Is it acceptably fast? If yes-and-yes, why change it? – ypercubeᵀᴹ
What is wrong with multiple left joins? And what do you mean by "better? If your query returns the correct results in an acceptable amount of time then just leave it alone. Otherwise you are spending time on something that has no value. – Pondlife
It does a look a bit 'hacky', especially if we use a lot of these LEFT JOINs. I was hoping there is some kind of mechanizm that could substitute them. – yosh

2 Answers

1
votes

I cannot understand why you want to change something that is working, but here's another way (which does those LEFT joins, but in a different way):

SELECT a.COlA, a.ColB, u.UserName,
  ( SELECT b.Value FROM TableB b WHERE a.EventId = b.EventId AND b.Code = 5 ),
  ( SELECT b.Value FROM TableB b WHERE a.EventId = b.EventId AND b.Code = 15 ),
  ( SELECT b.Value FROM TableB b WHERE a.EventId = b.EventId AND b.Code = 18 ),
  ( SELECT b.Value FROM TableB b WHERE a.EventId = b.EventId AND b.Code = 40 )

FROM TableA a JOIN Users u ON a.UserId = u.UserId

WHERE (a.UserId = 3) 

ORDER BY u.UserName ASC
1
votes
SELECT 
  a.COlA, a.ColB, u.UserName
  ,MAX(CASE WHEN b.Value = 5 THEN b.value ELSE 0 END) AS V5
  ,MAX(CASE WHEN b.Value = 15 THEN b.value ELSE 0 END) AS V15 
  ,MAX(CASE WHEN b.Value = 18 THEN b.value ELSE 0 END) AS V18 
  ,MAX(CASE WHEN b.Value = 40 THEN b.value ELSE 0 END) AS V45 
  ,COUNT(CASE WHEN b.Value not IN (5,15,18,40) THEN 1 ELSE NULL END) AS CountVOther
FROM TableA a 
INNER JOIN Users u ON a.UserId = u.UserId
LEFT  JOIN TableB b ON (a.EventId = b.EventId)
WHERE (a.UserId = 3) 
GROUP BY a.colA, a.colB, u.Username
ORDER BY u.UserName ASC