>I use this one in my SQL class from time to time....
>
>Suppose you have three tables - a Job Construction Master and 2 child tables (one for hours worked, and one for material purchases).
>
>At any one time, a job could have data in neither child table....or in both.....or in one but not the other.
>
>So here are the tables...
>
>

>CREATE TABLE JobMaster   (JobNumber int, JobDescription varchar(50))
>
>CREATE TABLE JobHours  (JobNumber int, EmployeeID int, DateWorked DateTime, 
>                        HoursWorked decimal(10,2))
>
>CREATE TABLE JobMaterials  (JobNumber int, Notes varchar(50), 
>                            purchaseamount decimal(14,2), 
>                            PurchaseDate DateTime)
>

>
>
>OK, here's some sample data...
>

>insert into JobMaster values (1, 'Job A')
>insert into JobMaster values (2, 'Job B')
>insert into JobMaster values (3, 'Job C')
>
>INSERT INTO JobHours values (1, 1, '01-01-08',8)
>INSERT INTO JobHours values (1, 1, '01-02-08',8)
>INSERT INTO JobHours values (1, 2, '01-01-08',7)
>INSERT INTO JobHours values (1, 2, '01-02-08',7)
>
>
>INSERT INTO JobHours values (2, 3, '01-01-08',7)
>INSERT INTO JobHours values (2, 3, '01-02-08',7)
>INSERT INTO JobHours values (2, 4, '01-01-08',6)
>INSERT INTO JobHours values (2, 4, '01-02-08',6)
>
>
>insert into JobMaterials values (1, 'bought tractor',1000,'1-1-08')
>insert into JobMaterials values (1, 'bought cement',2000,'1-1-08')
>
>insert into JobMaterials values (3, 'bought tractor',10000,'1-1-08')
>insert into JobMaterials values (3, 'bought cement',20000,'1-1-08')
>

>
>The goal: to write a query that returns a result set of one row per job, along with the summary of hours worked and material purchase amounts. So the result set might look something like this...
>
>

>--                Tot Hrs Worked      total of Material purchases
>--Job A      30                              3,000
>--Job B      26                              null
>--Job C      null                           30,000
>

>
>Given the table structure above and sample data - how would you write the SQL query? (Assume there are indexes on Job Number in every table, I was too lazy to include the syntax for that)

Are you sure to ask an as simple question for ?

USE tempdb

CREATE TABLE JobMaster   (JobNumber int, JobDescription varchar(50))

CREATE TABLE JobHours  (JobNumber int, EmployeeID int, DateWorked DateTime, 
                        HoursWorked decimal(10,2))

CREATE TABLE JobMaterials  (JobNumber int, Notes varchar(50), 
                            purchaseamount decimal(14,2), 
                            PurchaseDate DateTime)
                            
insert into JobMaster values (1, 'Job A')
insert into JobMaster values (2, 'Job B')
insert into JobMaster values (3, 'Job C')

INSERT INTO JobHours values (1, 1, '01-01-08',8)
INSERT INTO JobHours values (1, 1, '01-02-08',8)
INSERT INTO JobHours values (1, 2, '01-01-08',7)
INSERT INTO JobHours values (1, 2, '01-02-08',7)


INSERT INTO JobHours values (2, 3, '01-01-08',7)
INSERT INTO JobHours values (2, 3, '01-02-08',7)
INSERT INTO JobHours values (2, 4, '01-01-08',6)
INSERT INTO JobHours values (2, 4, '01-02-08',6)


insert into JobMaterials values (1, 'bought tractor',1000,'1-1-08')
insert into JobMaterials values (1, 'bought cement',2000,'1-1-08')

insert into JobMaterials values (3, 'bought tractor',10000,'1-1-08')
insert into JobMaterials values (3, 'bought cement',20000,'1-1-08')

--                Tot Hrs Worked      total of Material purchases
--Job A      30                              3,000
--Job B      26                              null
--Job C      null                           30,000

-- SOLUTION 1
SELECT
*
, [Tot Hrs Worked]=(SELECT SUM(HoursWorked) FROM JobHours WHERE JobNumber=J.JobNumber)
, [total of Material purchases]=(SELECT SUM(purchaseamount) FROM JobMaterials WHERE JobNumber=J.JobNumber)
FROM JobMaster J

-- SOLUTION 2
SELECT
J.*
, [Tot Hrs Worked]
, [total of Material purchases]
FROM JobMaster J
LEFT JOIN (SELECT JobNumber,[Tot Hrs Worked]=SUM(HoursWorked) FROM JobHours GROUP BY JobNumber) H ON H.JobNumber=J.JobNumber
LEFT JOIN (SELECT JobNumber,[total of Material purchases]=SUM(purchaseamount) FROM JobMaterials GROUP BY JobNumber) M ON M.JobNumber=J.JobNumber

DROP TABLE JobMaterials
DROP TABLE JobHours
DROP TABLE JobMaster