Another query problems

Question

my 1st table

type         days        application
-------------------------------
sick          10         leave
vacation      10         leave
PSCS           0         Seminar

my 2nd table

person             type           UseDays
-----------------------------------------
chuck norris      vacation             1
chuck norris      PSCS                 0
bruce lee         sick                 5

and i want to join them..but i want to view only the 'leave' in application in 1st table field

ex. i select chuck norris

type          days          UseDays
------------------------------------
sick           10                 0
vacation       10                 1

here's what i already done..but still can't get the right thing..

SELECT      LeavetypTbl.Id, LeavetypTbl.type , LeavetypTbl.days, ISNULL(SUM(LeaveTbl.UseDays), 0) AS Expr1, LeavetypTbl.days- ISNULL(SUM(LeaveTbl.UseDays), 0) as LeftX " & _
FROM        LeavetypTbl " & _
LEFT OUTER JOIN " & _
LeaveTbl " & _
ON          LeavetypTbl.type = LeaveTbl.type And LeaveTbl.IDno = '" & id & "' and LeaveTbl.Application <> 'Seminar'" & _
GROUP BY    LeavetypTbl.type , LeavetypTbl.days, LeavetypTbl.Id"

and the result...will still show all the application type..

Answer 1

Take smaller steps. Let's start with the types you want to have displayed. Let's filter these on leave. Let's call this result A:

type         days        application
-------------------------------
sick          10         leave
vacation      10         leave

Now let's look at the second table and filter it on the relevant person:

person             type           UseDays
-----------------------------------------
chuck norris      vacation             1
chuck norris      PSCS                 0

Now we summarize the UseDays, since there could be many vacations. The result looks quite similar. Let's call this B:

person             type      SumOfUseDays
-----------------------------------------
chuck norris      vacation             1
chuck norris      PSCS                 0

You know how to get to this point, right? Now let's join A and B on type (SELECT ... FROM (...select-for-a...) AS A LEFT JOIN (...select-for-b...) AS B ON A.type = B.type). Make sure to use a LEFT JOIN, since we want all records from A, even if no records in B exist:

type         days        application person      SumOfUseDays
-------------------------------------------------------
sick          10         leave       NULL              NULL
vacation      10         leave       chuck norris      1

We don't need the person column, so we just drop it. We still have this ugly NULL in SumOfUseDays, so we'll just COALESCE or ISNULL the result to 0:

type         days        application SumOfUseDays
-------------------------------------------------
sick          10         leave             0
vacation      10         leave             1

And there we are. The implementation is left as an exercise, but feel free to ask if you get stuck at a particular step.