Anthony Tanas wrote:

> "BTW, I really don't see why you use OUTER joins? They can make
> preformance really bad."
>
> So, if there is a charge but no payment or no refund associated then I will
> not get that result back, so I believe I need an outer join here? Does this
> make sense?

Not really, see below:

> SELECT DISTINCT
> DOCTOR.DOCTORID,
> DOCTOR.LASTNAME,
> DOCTOR.FIRSTNAME,
> DOCTOR.MIDDLEINITIAL,
> PATIENT.PATIENTID,
> PATIENT.CHARTID,
> PATIENT.LASTNAME,
> PATIENT.FIRSTNAME,
> PATIENT.MIDDLEINITIAL
> FROM
> PATIENT
> INNER JOIN BILLING ON (PATIENT.PATIENTID = BILLING.PATIENTID)
> INNER JOIN BILLINGITEM ON (BILLING.BILLINGID = BILLINGITEM.BILLINGID)
> LEFT OUTER JOIN PAYMENT ON (BILLING.BILLINGID = PAYMENT.BILLINGID)
> LEFT OUTER JOIN REFUND ON (BILLING.BILLINGID = REFUND.BILLINGID)
> INNER JOIN DOCTOR ON (BILLING.DOCTORID = DOCTOR.DOCTORID)
> WHERE
> BILLINGITEM.BILLINGITEMDATE = :BILLINGITEMDATE OR
> PAYMENT.PAYMENTDATE = :BILLINGITEMDATE OR
> REFUND.REFUNDDATE = :BILLINGITEMDATE

Although you use OR (which confused me first), it doesn't look like the
right way to do it. If I understood you correctly, prehaps it would be
better if you rewrite your joins like this:

LEFT OUTER JOIN PAYMENT ON (BILLING.BILLINGID = PAYMENT.BILLINGID
AND PAYMENT.PAYMENTDATE = :BILLINGITEMDATE)
LEFT OUTER JOIN REFUND ON (BILLING.BILLINGID = REFUND.BILLINGID
AND REFUND.REFUNDDATE = :BILLINGITEMDATE)

and remove those two from the where clause. It would also make some of
the HAVING stuff unneeded, and it might speed up the query.

--
Milan Babuskov
http://swoes.blogspot.com/
http://www.flamerobin.org