SUM ONLY of first table wiht inner join of second table 1

[DFW1999]

Hello,

I have First table:
CREATE TABLE [dbo].[Table_A] (
[CUT_NO] [int] NULL ,
[FG_ITEM] [char] (10),
[FG_SEASON] [char] (10) ,
[APPROVAL] [char] (10) ,
[QUANTITY] [int] NULL
) ON [PRIMARY]
GO
select * from Table_A;
OUTPUT:
1 FIRST JAN NULL 10
2 SECOND FEB NULL 20
3 THIRD MAR NULL 30
4 FOURTH APRIL NULL 40
5 FIFTH MAY NULL 50
1 FIRST JAN NULL 10
2 SECOND FEB NULL 20
3 THIRD MAR NULL 30
4 FOURTH APRIL NULL 40
5 FIFTH MAY NULL 50

Second table is Table_B
CREATE TABLE [dbo].[Table_B] (
[CUT_NO] [int] NULL ,
[FG_ITEM] [char] (10),
[FG_SEASON] [char] (10) ,
[APPROVAL] [char] (10) ,
[QUANTITY] [int] NULL
) ON [PRIMARY]
GO

OUTPUT:
1 FIRST JAN true 10
2 SECOND FEB true 20
3 THIRD MAR NULL 30
4 FOURTH APRIL true 40
5 FIFTH MAY true 50
1 FIRST JAN true 10
2 SECOND FEB true 20
3 THIRD MAR NULL 30
4 FOURTH APRIL true 40
5 FIFTH MAY true 50


I want to get all the fields of table_A along with sum(A.quantity) where every thing has a inner join with Table_B and I am only interested those records in table B where Approval value is 'true'.

Here is the query which I wrote:

select A.CUT_NO, A.FG_ITEM, A.FG_SEASON, A.APPROVAL,sum(A.QUANTITY)
from Table_A A join Table_B B
on
A.CUT_NO=B.CUT_NO
AND A.FG_ITEM=B.FG_ITEM
AND A.FG_SEASON=B.FG_SEASON
and a.quantity=b.quantity
where b.APPROVAL='true'
group by A.CUT_NO, A.FG_ITEM, A.FG_SEASON, A.APPROVAL

Here is the outPUT:
1 FIRST JAN true 40
2 SECOND FEB true 80
4 FOURTH APRIL true 160
5 FIFTH MAY true 200

Here I am expecting to sum only table_A's quantity. In this case if you see A.CUT_NO 1 I want only Table_A's quantity which is 20. But as the result of inner join it adds up to double in this case 40.
Is there any suggestion how to sum up only table_A's quantity where Approval attribute of TABLE_B='true'. I am only interested in those records in table_B which have 'APPROVAL' value 'true' and those records are present in Table_A as well
Any suggestion?

 

[r937]

select CUT_NO
     , FG_ITEM
     , FG_SEASON
     , APPROVAL
     , sum(QUANTITY) as sumquantity
  from Table_A 
 where exists
     ( select 1
         from Table_B  
        where CUT_NO    = Table_A.CUT_NO
          and FG_ITEM   = Table_A.FG_ITEM
          AND FG_SEASON = Table_A.FG_SEASON
          and quantity  = Table_A.quantity
          and APPROVAL = 'true'
     )
group 
    by CUT_NO
     , FG_ITEM
     , FG_SEASON
     , APPROVAL

rudy

http://r937.com/

SQL Consulting

 

[PHV]

Another way without correlated subquery:
select A.CUT_NO, A.FG_ITEM, A.FG_SEASON, A.APPROVAL,sum(A.QUANTITY)
from Table_A A join
(SELECT CUT_NO, FG_ITEM, FG_SEASON, quantity FROM Table_B
WHERE APPROVAL='true'
GROUP BY CUT_NO, FG_ITEM, FG_SEASON, quantity) B
on A.CUT_NO=B.CUT_NO
AND A.FG_ITEM=B.FG_ITEM
AND A.FG_SEASON=B.FG_SEASON
and a.quantity=b.quantity
group by A.CUT_NO, A.FG_ITEM, A.FG_SEASON, A.APPROVAL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

ah, but WHERE EXISTS might be faster

have you read this: How to Misuse SQL's FROM Clause

rudy

http://r937.com/

SQL Consulting

 

[dnoeth]

If there are two rows for
"1 FIRST JAN 10"
in each table, but only one row in table_B has Approval = 'true'
what's the expected result?

10 or 20?

20: Rudy's or PHV's query

10:
select CUT_NO
, FG_ITEM
, FG_SEASON
, APPROVAL
, QUANTITY as sumquantity
from
(
select CUT_NO
, FG_ITEM
, FG_SEASON
, approval
, QUANTITY
from Table_B
where approval = 'true'

INTERSECT ALL

select CUT_NO
, FG_ITEM
, FG_SEASON
, 'true'
, QUANTITY
from Table_A
) dt
group
by CUT_NO
, FG_ITEM
, FG_SEASON
, APPROVAL

This is the first time i ever found INTERSECT ALL to be usefull ;-)

But the create table looked like MS SQL and MS doesn't support Intersect...

Btw, for the example data there's an easy solution ;-)

SELECT CUT_NO, FG_ITEM, FG_SEASON, APPROVAL, sum(quantity)
FROM Table_B
WHERE APPROVAL='true'
GROUP BY CUT_NO, FG_ITEM, FG_SEASON

Dieter