I am trying to write a query that will return APINVOICE, APDISTRIB, APVENMAST, and APPAYMENT information.

Basically the user wants the Paid invoice detail information including vendor name and check number & date.

I created the following query but it doesn't seem to completely work the way I would like it to do. It pulls the same invoice & distrib twice for each combination. In other words, if there is a single invoice with 2 distribution records, instead of the query returning 2 records it actually returns 4. this seems to happen when I include the part of the query where I add the logic to pull the appayment info (check date, number). If I remove the appayment stuff, the query seems to work fine.

Any thoughts?

select p.company, a.vendor, a.vendor_vname, p.invoice, p.invoice_dte, p.due_date, p.create_date,
p.distrib_date, d.dis_acct_unit, d.dis_account, y.bank_chk_amt, d.to_base_amt, y.check_date, y.trans_nbr
from uabhssd.apvenmast a,
uabhssd.apinvoice p,
uabhssd.appayment y,
uabhssd.apdistrib d
where (
p.company = d.company and
p.vendor = d.vendor and
p.invoice = d.invoice and
a.vendor = d.vendor and
y.company = p.company and
y.vendor = p.vendor and
y.invoice = p.invoice and
p.tran_paid_amt <> 0
)

Hi Karl, the problem is that you are doing an "inner join" (by default), which returns a combined row for all joined tables. You need to use an outer join to achieve the desired result. The syntax for outer joins differs from database vendor to database vendor, but if you're using Oracle you could probably do something like this (also, you were missing some fields in the joins):

select api.company, ven.vendor, ven.vendor_vname, api.invoice, api.invoice_dte, api.due_date, api.create_date,
api.distrib_date, apd.dis_acct_unit, apd.dis_account, app.bank_chk_amt, apd.to_base_amt, app.check_date, app.trans_nbr
from
uabhssapd.apinvoice api,
uabhssapd.appayment app,
uabhssapd.apdistrib apd,
uabhssapd.apvenmast ven
where (
api.company = apd.company (+) and
api.vendor = apd.vendor (+) and
api.invoice = apd.invoice (+) and
api.suffix = apd.suffix (+) and
api.cancel_seq = apd.cancel_seq (+) and
ven.vendor_group = api.vendor_group (+) and
ven.vendor = api.vendor (+) and
app.company = api.company (+) and
app.vendor = api.vendor (+) and
app.invoice = api.invoice (+) and
app.suffix = api.suffix (+) and
app.cancel_seq = api.cancel_seq (+) and
api.tran_paid_amt <> 0
)