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

The problem is that there is no link between table B and table C... example, in your result you show that the 100$ for employee 1 was paid for his "Basic Hours" work... but how do we know that... where in your data do we know that the 100$ 'Payment' wasn't for his 'Holiday Pay'? — Timothy Khouri
– Timothy Khouri, Commented Jan 20, 2010 at 10:32

Quassnoi · Accepted Answer · 2010-01-20 10:32:00Z

3

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)

answered Jan 20, 2010 at 10:32

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

help required with Sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related