SQL: Using one table as a criteria for itself

Question

I am trying to use a parameter of a table as a criteria for itself, and can't quite get my sql statement right. It seems to be a relatively simple query; I'm using a sub query for my criteria, but it is not filtering out other rows on my table.

Background:

Manufacturing production floor: I have a bunch of machinists on their machines right now running an operation (OprSeq) of a job (JobNum). From the LaborDtl table, which keeps a record of all labor activity, I can see what labor is currently active (ActiveTrans = 1). With this criteria of active labor, I want to sum up all the past labor transactions on each active labor entry. So I need a LaborDtl table of inactive labor activity with the criteria of active labor from the same table.

The code:

Heres my 'criteria' subquery:

SELECT
    LaborDtl.JobNum,
    LaborDtl.OprSeq

    FROM Erp.LaborDtl

    WHERE LaborDtl.ActiveTrans = 1

Which returns active transactions, here's the first couple (sorted by job):

Job     Operation
000193  90
000457  70
000457  70
020008-1    140
020008-2    130
020010  60
020035  130
020175  40
020175-2    50
020186  80
020199  10
020203  50
020212  40
020258  60
020272  10
020283  30
020298  10
020299  30

Then here's the full SQL Statement, with the query above embedded:

SELECT
LaborDtl.JobNum,
LaborDtl.OprSeq as "Op",
SUM(LaborDtl.LaborQty) as "Total Labor"


FROM Erp.LaborDtl

  WHERE EXISTS
    (
    SELECT
    LaborDtl.Company,
    LaborDtl.JobNum,
    LaborDtl.OprSeq

    FROM Erp.LaborDtl

    WHERE LaborDtl.ActiveTrans = 1 --Labor table of just current activity
    )
GROUP BY LaborDtl.JobNum, LaborDtl.OprSeq

I expect to see only the Job and Operation numbers that exist in my sub query, but I'm getting both jobs and operations that don't exist in my sub query. Here are the first 10 (note, the first JobNum should be 000193 per my criteria)

   JobNum   Op  Total Labor
            0   0.00000000
   000004   1   32.00000000
   000019   1   106.00000000
   000029   1   175.00000000
   000143   1   85.00000000
   000164   1   58.00000000
   000181   1   500.00000000
   000227   1   116.00000000
   000421   1   154.00000000
   000458   1   67.00000000

Answer 1

You're missing some condition to tie the outer and inner queries together. Right now, without that criteria, the inner query just returns "true", as there are jobs with active activities and thus all the rows in the outer query are returned. Note that you'll have to add aliases to the tables, as the inner and outer query use the same table:

SELECT   a.JobNum, a.OprSeq as "Op", SUM(a.LaborQty) as "Total Labor"
FROM     Erp.LaborDtl a
WHERE    EXISTS (SELECT * -- The select list doesn't really matter here
                 FROM   Erp.LaborDtl b
                 WHERE  a.JobNum = b.JobNum AND -- Here!
                        a.OprSeq = b.OprSeq AND -- And here!
                        b.ActiveTrans = 1 -- Labor table of just current activity
                )
GROUP BY a.JobNum, a.OprSeq

Note, however, that there's an easier (IMHO) way. Since you're grouping by JobNum and OprSeq anyway, you could just count the number of active transactions and using a having clause to query only those that have at least one active transaction:

SELECT   JobNum, OprSeq as "Op", SUM(LaborQty) as "Total Labor"
FROM     Erp.LaborDtl
GROUP BY JobNum, OprSeq
HAVING   COUNT(CASE ActiveTrans WHEN 1 THEN 1 END) > 0