help required with Sql query

Question

I have a problem , got three Tables

Table A 

ID        Employee    
1         1    
2         2
3         3


Table B
Id        EMployee        HoursWorked         HoursCode
1          1                 10               Basic Hours
2          1                 20               Holiday Pay
3          2                 10               Basic hours
4          2                 15               OverTime

Table C 
ID        Employee         Payments            PayCode
1           1                 100              Bonus
2           2                 150              Bonus
3           2                 250              Student Loan

I want to get the records out of these table in minimum lines , so i can have one line which says

id        Employee          Hour               HoursCode       Payments      PayCode
1          1                 10                Basic Hours     100           Bonus     
2          1                 20                Holiday Pay      null         null
3          2                 10                basic hours      150          Bonus 
4          2                 15                 OverTime        250          Student loan

I have spent ages trying to get it ... But dont get the Null in the 2nd line it comes out with 100 Bonus in second line for employee 1

is there way i can do this Please Help

Answer 1

WITH    bn AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY employee ORDER BY id) AS rn
        FROM    b
        ),
        cn AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY employee ORDER BY id) AS rn
        FROM    c
        )
SELECT  *
FROM    bn
FULL JOIN
        cn
ON      bn.employee = cn.employee
        AND bn.rn = cn.rn
ORDER BY
        COALESCE(bn.employee, cn.employee), COALESCE(bn.rn, cn.rn)