There is no intersection table.

The PAYMENT table has a BILLINGITEMID foreign key, although I have not
explicitly set up any foreign keys in my database at this point.

I understand that Unions require that the two tables be structurally the
same, can I have blanks in one output set?

The table structures(excluding irrelevant fields) are as follows. I know I
have some mistakes in my design that I need to remedy shortly.

I was under tremendous stress and pressure when I was converting from
Paradox to Firebird and I did it in a rediculously short amount of time and
so I have a few problems (maybye you can advise me on the best way to go
about fixing these as well, I have live beta sites so I definitely have to
have some plan for conversion):

1) Used Float datatype for my primary key instead of integer
2) Used Float datatype for currency instead of specific precision types
3) I have some redundant data and primary keys with more fields then
necessary

All (FK)s indicated are functional, I didn't actually set them up in
firebird. I won't indicate FKs for non included tables.

BILLINGITEM
----------------
(PK)BILLINGITEMID (FLOAT) [Functionally unique, should be the PK alone]
(PK)BILLINGID (FLOAT) (FK BILLING.BILLINGID)
BILLINGITEMDATE (DATE)
PROCEDURECODE (VARCHAR 25)
DIAGNOSISCODE (VARCHAR 25)
AMOUNT (FLOAT) [should used fixed precision datatype]
UNITS (FLOAT) [should be INT]
PRIMARYINSURANCE (INTEGER) (FK INSURANCE.INSURANCEID)
SECONDARYINSURANCE (INTEGER) (FK INSURANCE.INSURANCEID)

PAYMENT
-----------------
(PK) PAYMENTID (FLOAT)
BILLINGITEMID (FLOAT) (FK BILLINGITEM.BILLINGITEMID)
BILLINGID (FLOAT) (FK BILLING.BILLINGID) [Redundant data]
PAYMENTDATE (DATE)
PAYTYPE (VARCHAR 25)
AMOUNT (FLOAT) [Again, wrong datatype]

The following tables need to be joined to get insurance name and to select
specific patient, again I'll only include relevant fields.

BILLING
------------------
(PK) BILLINGID (FLOAT)
PATIENTID (FLOAT)

INSURANCE
------------------
(PK) INSURANCEID (FLOAT)
INSURANCECODE (VARCHAR 30)

This is the data I would like to get, I'll include blanks in the payment
part to keep them structurally the same:

BILLINGLINE:
---------------
1) INSURANCECODE (BILLINGITEM.PRIMARYINSURANCE) (NEED OUTER JOIN SINCE COULD
BE NO INSURANCE)
2) INSURANCECODE (BILLINGITEM.SECONDARYINSURANCE) (NEED OUTER JOIN SINCE
COULD BE NO INSURANCE)
3) BILLINGITEMDATE
4) PROCEDURECODE
5) DIAGNOSISCODE
6) AMOUNT
7) UNITS
8) AMOUNT * UNITS (EXTENDED)

PAYMENTLINE:
----------------
1) BLANK
2) BLANK
3) PAYMENTDATE
4) PAYTYPE
5) BLANK
6) BLANK
7) BLANK
8) AMOUNT

On the BILLINGLINE I would also like the following but whenever I try to do
this in one query my query gets EXTREMELY slow (i.e. MINUTES)...in the past
when I have needed similar information I have created calculated fields in
my dataset and then ran a separate query to get the information.

BILLINGLINE (con't):
----------------------
9) SUM(PAYMENT.AMOUNT)
10 EXTENDED - SUM(PAYMENT.AMOUNT) BALANCE

Previously I got SUM(PAYMENT.AMOUNT) through a VIEW I had created called
TOTALBIPAYMENTS (SELECT DISTINCT BILLINGITEMID,SUM(AMOUNT) FROM PAYMENT
GROUP BY BILLINGITEMID)

OK, I know I've made some mistakes :p ... I went through a period where I
had to get things working in an extremely short time frame or lose my
opportunity all together. But now I'm in a much better situation with my
project and have the time, desire and inclination to get things right.

I am humbly gratefull for your and everyone else's assistance. :)

-Anthony

> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
> Sent: Sunday, March 12, 2006 12:39 AM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] Is it possible to do this query?
>
> At 04:15 PM 12/03/2006, you wrote:
> >Yes, Billing is related to Payment in a one to many
> relationship. So I
> >kind of want a "transaction log" where I have one billing
> line followed
> >by all the payments on that line.
>
> OK, so what does the referential relationship look like? i.e.
> which column in Payment references the primary key or unique
> constraint in Billing?
>
>
> >It's medical so each item has payments applied specifically to it.
> >
> >Typically the co-pay, then an insurance payment and then a
> write off. I am
> >actually currently reading in your book about Unions as Adam
> suggested...am
> >I barking up the right tree?
>
> A union is the only way you are going to get columns (which must be
> type-compatible) from different tables to output in different rows of
> a single set.
>
> But more to the point at the moment is what you've got to correlate
> the many payments to the single billing. Since you don't have it in
> the structure you provided, I assume you have an intersection table?
>
> ./heLen
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>