tricky sql

From: David Bleckler <bleckled_at_attmail.noralnm.com>
Date: 1998/04/02
Message-ID: <01bd5e44$605656a0$8a01020a@bleckled.noralnm.com>#1/1

I have 3 tables, POLMAS (purchase order line master), PRCMAS (po receipts), and INLMAS (po invoice lines).

All 3 tables have the fields POHNUM (po header #), RELNUM (release #) and POLLIN (line #).

There is a one-to-many relationship between POLMAS and PRCMAS and between POLMAS and INLMAS.
There is NO relationship between PRCMAS and INLMAS.

I would like a query/report that returns the following:

POHNUM, POLLIN, QTYREC, QTYINV where QTYREC is quantity received (from PRCMAS) and QTYINV is quantity invoiced (from INLMAS).

My problem is that if I have an order line with two receipts and two invoices, it returns four rows. I would like it to only return two. Likewise, if I have a po line with three invoices and two receipts, it should return three rows with the QTYREC column empty.

I believe I need to use (two) outer joins but I have been unsuccessful so far.
Creating views is an option also available.

Any help/suggestions would be appreciated. Received on Thu Apr 02 1998 - 00:00:00 CST