I've got to querys.. that i want to converge into one, but i'm not sure how to do it..
Code:
The first.

SELECT Stock.Stock_ID
     , Stock.Amount_Units 
     , COALESCE(SUM(Items_Sold.Amount_Units),0) AS Sold_Units
     , Stock.Amount_Units -  
       COALESCE(SUM(Items_Sold.Amount_Units),0) AS Units_Left
  FROM Stock
LEFT OUTER
  JOIN Items_Sold
    ON Items_Sold.Stock_ID = Stock.Stock_ID
GROUP
    BY Stock.Stock_ID
     , Stock.Amount_Units 

Results in 

Stock_ID	Amount_Units	       Sold_Units	Units_Left
1	            1	                        1	0
2	            1	                       0	1
3	            1                          0	1
4	            1	                       0	1
29	            3	                       0	3
30	            1	                       0	1
37	            1	                       0	1

ETC...

The second is 
SELECT Job.Job_ID, SUM(Stock.Amount_Units)

FROM Job
LEFT JOIN Stock
ON Job.Job_ID = Stock.Job_ID

GROUP BY Job.Job_ID

Job_ID	SUM(Stock.Amount_Units)
1	          3
4	          3
5	          5
6	          4
7	          13
8	          NULL
From the first query, each Stock_ID is represent by a Job_ID.. I.e, Job_ID 1 could contain Stock_ID 1/2 and 7.

I need to be able to get the Amount_Units from every Stock_ID that is linked to a Job_ID (This is the second query).. But then i want to link that to the first query, where i get the Amount.Units left for each Job_ID.

Code:
Stock_ID 4 .Amount_Units = 3 > 0 Units are left }
                                                                    > Job_ID 1 = 4 Units left
Stock_ID 6 .Amount_Units = 7 > 4 Units are left }
And then after that, I need to count the amount of Job_ID's that have 0 Units_Left

This is too complex for a first year
Thanks for any help!

/Insidus