Query from different tables

I have 2 tables

TABLE A

INV    AMT   DISC
1001  1500    150
1002  3000    300

TABLE B

INV  DESC        AMT
1001 CHARGES     100
1001 FREIGHT      30
1001 INSURANCE    20
1002 CHARGES     215
1002 FREIGHT      32
1002 INSURANCE    25

To combine both tables, I used the following query (given by Mikael Eriksson):-

select
    A.inv,
    A.amount,
    A.disc,
    B.charges,
    B.freight,
    B.insurance
from @TableA as A
    inner join (
                SELECT t.inv,
                       MAX(CASE WHEN t.description = 'CHARGES' THEN t.amount ELSE NULL END) AS charges,
                       MAX(CASE WHEN t.description = 'FREIGHT' THEN t.amount ELSE NULL END) AS freight,
                       MAX(CASE WHEN t.description = 'INSURANCE' THEN t.amount ELSE NULL END) AS insurance
                FROM @TableB as t
                GROUP BY t.inv) as B
        on A.inv = B.inv    

Then, I'll have the following output:-

INV     AMT   DISC    CHARGES FREIGHT INSURANCE
1001   1500    150      100      30       20
1002   3000    30开发者_如何转开发0      215      32       25

Question, how can I add a query into the earlier statement if I want say, where charges is equal to 100. The final result will look like this:-

INV     AMT   DISC    CHARGES FREIGHT INSURANCE
1001   1500    150      100      30       20

select
    A.inv,
    A.amt,
    A.disc,
    B.charges,
    B.freight,
    B.insurance
from TableA as A
    inner join (
                SELECT t.inv,
                       MAX(CASE WHEN t.descr = 'CHARGES' THEN t.amt ELSE NULL END) AS charges,
                       MAX(CASE WHEN t.descr = 'FREIGHT' THEN t.amt ELSE NULL END) AS freight,
                       MAX(CASE WHEN t.descr = 'INSURANCE' THEN t.amt ELSE NULL END) AS insurance
                FROM TableB as t
                GROUP BY t.inv) as B
        on A.inv = B.inv
where charges = 100   --<< just add this

Depending on how many rows contain tableb.amt = 100 and descr = 'charges', you may be better off writing the query this way.

select a.inv, a.amt, a.disc,
  b.amt AS charges,
  c.amt AS freight,
  d.amt AS insurance
from tablea a
inner join tableb b on b.inv = a.inv and b.descr = 'CHARGES' and b.amt = 100
left join tableb c on c.inv = a.inv and c.descr = 'FREIGHT'
left join tableb d on d.inv = a.inv and d.descr = 'INSURANCE'

Test Data

create table tablea (inv int, amt int, disc int);
insert tablea select 1001,1500,150;
insert tablea select 1002,3000,300;

create table tableb (inv int, descr varchar(10), amt int);
insert tableb select 1001, 'CHARGES', 100;
insert tableb select 1001, 'FREIGHT', 30;
insert tableb select 1001, 'INSURANCE', 20;
insert tableb select 1002, 'CHARGES', 215;
insert tableb select 1002, 'FREIGHT', 32;
insert tableb select 1002, 'INSURANCE', 25;

Output

inv         amt         disc        charges     freight     insurance
----------- ----------- ----------- ----------- ----------- -----------
1001        1500        150         100         30          20
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

_{Note: Using the proposed 2nd query, the warning is not present in the output}