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.

Solution 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

Solution 2

select
    C.CompanyID, C.CompanyName, count(distinct CaseID)
from Company C
    inner join Workers W
        on C.CompanyID = W.WorkerID
    inner join (
        select CaseId, WorkerId = Worker1 from Cases where Worker1 is not null
        UNION ALL
        select CaseId, WorkerId = Worker2 from Cases where Worker2 is not null
        UNION ALL
        select CaseId, WorkerId = Worker3 from Cases where Worker3 is not null
        ) CW
        on W.WorkerID = CW.WorkerID
group by C.CompanyID, C.CompanyName

Solution 3

I'd recommend changing your schema a little like so:

cases - caseid, casenumber
workers - workerid, companyid
cases_workers - caseid, workerid
company - companyid, companyname

If you had it this way, you could write:

select companyname, count(*)
from company c
inner join workers w on c.companyid = w.companyid
inner join cases_workers cw on w.workerid = cw.workerid
group by companyname

EDIT:

If you cannot change schema, use some of the good queries already mentioned by other commenters. Here's my version:

with caselist (
  select worker1 as worker from cases union all
  select worker2 as worker from cases union all
  select worker3 as worker from cases
)
select companyname, count(*)
from company c
inner join workers w on c.companyid = w.companyid
inner join caselist cl on w.workerid = cl.worker
group by companyname

Comments

[Ian Yates]

Agreed - the schema change is the right way to go. But not always possible :(

[Ian Yates]

See other comments about schema changes. But if you can't change it you can at least cheat and unpivot (syntax I can never remember off the top of my head so I used the UNION ALLs instead) to make the table virtually

[zedfoxus]

@IanYates, yes, I agree with you. Let me edit my answer and add query that could be used with current schema

[Dhwani]

Sorry, but i can't change schema as it is already built and database is on production.

[Ian Yates]

I like this one more than mine actually since it does what it says on the tin and nothing else. Missing a group by though.

[Mureinik]

@IanYates Yikes! don't know what happened there. You are correct, of course. I've edited the group by in.

[zedfoxus]

This is a nice, concise query! Cool!

[zedfoxus]

@dotNetAddict that is understandable. I have edited my answer appropriately keeping the query somewhat similar to the previous one I wrote. Also remember that someday you will have to change the schema; doing sooner might be a better option than doing it later.