Jevin,

You did not specify SQl Server version.

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