>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)

I think it could be solved using new OVER syntax, but I write it right from the top of my head using derived tables (the simplest case for me). [I haven't checked other responses yet]

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')

select JobMaster.*, cast(ISNULL(JM.Bought,0) as int) as MaterialsBought,  
CAST(isnull(JH.HoursWorked, 0) as int) as HoursWorked from JobMaster 
LEFT JOIN (select JobNumber, SUM(purchaseamount) 
as Bought from JobMaterials group by JobNumber ) JM on
JobMaster.JobNumber = JM.JobNumber LEFT JOIN 
(select JobNumber, SUM(HoursWorked) as HoursWorked from JobHours group by JobNumber) JH 
on JobMaster.JobNumber = JH.JobNumber