i can't get this query right

Question

i got a two table the 1st one looks like this

Leave       Limit
-----------------
vacation      15
sick          10
absent         9

and the second one..

Person             Leave      Use
-------------------------------
Chuck Norris     sick        1
Bruce Lee        absent      2

what i want it to do is to join the tables and when i select a person it will display like this

ex. i select Chuck Norris 

Leave        Limit        Use
------------------------------
vacation       15          0
sick           10          1
absent          9          0

Here's what i've done already....

SELECT        LeavetypTbl.Leave, LeavetypTbl.Limit, ISNULL(SUM(LeaveTbl.Use), 0) AS Expr1, 
              LeavetypTbl.Limit- ISNULL(SUM(LeaveTbl.USE), 0) AS DayLeft
FROM          LeavetypTbl LEFT OUTER JOIN
              LeaveTbl ON LeavetypTbl.Leave = LeaveTbl.Leave
WHERE         LeaveTbl.Person ='Chuck Norris'
GROUP BY LeavetypTbl.Leave, LeavetypTbl.Limit

but the result is not what i expect it..lol

Leave    Limit    Use    DayLeft

---

Sick       10       1          9

i only get 1 row will..

Answer 1

You get only one row because you are limiting the results to only 'Chuck Norris' in the top-level WHERE clause. However, Chuck Norris never took vacations or absences, so the other two rows from the outer join got eliminated by the WHERE clause. Move it to the ON clause to get the results that you expect:

SELECT        LeavetypTbl.Leave, LeavetypTbl.Limit, ISNULL(SUM(LeaveTbl.Use), 0) AS Expr1, 
              LeavetypTbl.Limit- ISNULL(SUM(LeaveTbl.USE), 0) AS DayLeft
FROM          LeavetypTbl LEFT OUTER JOIN
              LeaveTbl ON LeavetypTbl.Leave = LeaveTbl.Leave AND LeaveTbl.Person ='Chuck Norris'
GROUP BY LeavetypTbl.Leave, LeavetypTbl.Limit

Answer 2

add person filter in JOIN condition like this:

SELECT        LeavetypTbl.Leave, LeavetypTbl.Limit, ISNULL(SUM(LeaveTbl.Use), 0) AS Expr1, 
              LeavetypTbl.Limit- ISNULL(SUM(LeaveTbl.USE), 0) AS DayLeft
FROM          LeavetypTbl LEFT OUTER JOIN
              LeaveTbl ON LeavetypTbl.Leave = LeaveTbl.Leave
                          and LeaveTbl.Person ='Chuck Norris'
GROUP BY LeavetypTbl.Leave, LeavetypTbl.Limit