I have a parent table Orders
and a Child table Jobs
with the following sample data
I want to select Orders based on the following requirements
1>For each order there may be 0 or more jobs. Do not select order if it does not have any job.
2>A user cannot work on more than one job that belongs to the same order.
For example User 1
cannot work on the Jobs that belongs to Order 1 and 2 because he already worked on jobs 1
and 4
from the same order.
3>Only select orders that have jobs in Requested
status
I have the following query that gives me expected result
DECLARE @UserID INT = 2
SELECT O.OrderID
FROM Orders O
JOIN Jobs J ON J.OrderID = O.OrderID
WHERE
J.JobStatus = 'Requested' AND
NOT EXISTS
(
--Must not have worked this Order
SELECT 1 FROM Jobs J1
WHERE J1.OrderID = O.OrderID AND J1.UserID = @UserID
)
Group By o.OrderID
Query joins the Jobs
table twice. I am trying to optimize query and looking for a way to achieve the expected result by using Jobs
table only once if possible. Any other solution is also appreciated. I can alter the table schema if required.
The jobs table has almost 20M rows and some time query shows poor performance. (Yes, we looked at indexes). I think its scanning jobs table twice causing the performance issue.
ID
of type int. Just for understanding purpose I kept it as nvarchar