RE: SQL Statement Help

If you use a left outer join it will preserve the rows in A that have no
match in B or C. The Table B or C information in said rows will come
back as a NULL, and you can use coalesce to handle the NULLs.

Select
 a.ptno,
 a.ptcl,
 (a.ptqty - coalesce(b.ptqty,0) - coalesce(c.ptqty,0)) as "QTY" 
from 
TableA as a left outer join
TableB as b on
  a.ptno = b.ptno and 
  a.ptcl = b.ptcl left outer join
TableC as c on
  a.ptno = c.ptno and 
  a.ptcl = c.ptcl

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Brian Piotrowski
Sent: Monday, June 13, 2005 4:59 PM
To: RPG programming on the AS400 / iSeries
Subject: SQL Statement Help

Hi All,

 

I apologize in advance if this is not the proper forum, but I know
there's a bunch of SQL-savvy people in here. :-)

 

I have three tables (for simplicity, I'll include only mock data):

 

Table A:

PTNO        PTCL        QTY

A000        Black       500

B000        White       1000

C000        Red         2000

D000        Blue        5000

 

Table B:

PTNO        PTCL        QTY

A000        Black       300

B000        White       600

C000        Red         1200

D000        Blue        2000

 

Table C:

PTNO        PTCL        QTY

A000        Black       100

B000        White       200

C000        Red         200

 

I want to take the qty value of TableA and subtract the qty value of
TableB and TableC.  However, if the ptno does not appear on either Table
B or Table C, I want to make it 0.

 

So far, I have it telling me the QTY value when there are similar PTNOs
in Table B and Table C using this statement:

 

"Select a.ptno, a.ptcl, (a.ptqty - b.ptqty - c.ptqty) as "QTY" from
TableA as a, TableB as b, TableC as c where a.ptno = b.ptno and a.ptcl =
b.ptcl and a.ptno = c.ptno and a.ptcl = c.ptcl"

 

Running it against the current data, I get:

PTNO        PTCL        QTY

A000        Black       100

B000        White       200

C000        Red         600

 

However, this statement doesn't take into account where there are parts
on TableA, TableB but not in TableC or when there are parts in TableA
and TableC, but not TableB.  Also, if the part is in TableA, but not
either TableB or TableC, it will not display the part.

 

Can someone give me an idea on what I need to add to my SQL code to get
it to show a value of 0 if the part exists in TableA but does not exist
in TableB or TableC or both tables?

 

So in actuality, I want the code to return this value:

PTNO        PTCL        QTY

A000        Black       100

B000        White       200

C000        Red         600

D000        Blue        3000

 

Thank you!

 

Brian.

 

-=-=-=-=-=-=-=-=-=-=-=-=-=-

Brian Piotrowski

Specialist - I.T.

Simcoe Parts Service, Inc.

Ph: 705-435-7814 x343

Fx: 705-435-6746

bpiotrowski@xxxxxxxxxxxxxxx

-=-=-=-=-=-=-=-=-=-=-=-=-=-