SQL JOIN WITH OR Condition

Question

I have a table say Cases, which is using reference from Workers for three columns. Also there is one table Company to which workers belongs.

Below is the schema:

Cases [ CaseID, CaseNumber, Worker1, Worker2, Worker3 ] 
Workers [ WorkerID, ComapnyID]
Company [CompanyID, CompanyName]

Now I need case count for each company. So is it possible to make one join with workers and map all Worker1, Worker2 and Worker3 columns? Is there any better option and performance impact?

Note: Two workers from one company can work on single case, or all the workers can be from different companies.

Answer 1

Although join conditions are commonly equality checks, there's nothing special about them - any valid SQL condition could be used for performing a join. In you case, an IN condition seems appropriate:

SELECT   CompanyName, COUNT(DISTINCT CaseID)
FROM     Company co
JOIN     Workers w ON co.CompanyId = w.CompanyId
JOIN     Cases ca ON w.WorkerId IN (ca.Worker1, ca.Worker2, ca.Worker3)
GROUP BY CompanyName