I have a set of related tables: Customer Accounts(Acct), real estate Parcel(real), Personal Propertry (Pers) and Flat Charges(flat). Real, Pers and Flat are always tied to one Acct record. In addition, tables: Real, Pers and Flat may also be related between themselvs by a parcel id code. If I build data files as diagrammed below (a test case), I need an SQL Select that would build and join records the same way:

Acct Real Pers Flat
acct|parcel acct|parcel acct|parcel
(1) aaaa aaaa|1111 aaaa|1111 aaaa|1111
(2) aaaa aaaa|2222 aaaa|2222 no data
(3) aaaa aaaa|3333 no data aaaa|3333
(4) aaaa no data aaaa|4444 aaaa|4444
(5) aaaa aaaa|5555 no data no data
(6) aaaa aaaa|no data aaaa|6666 no data
(7) aaaa no data no data aaaa|7777


"aaaa" represents a particular account, "1111", "2222", and so on represent parcel Id codes. "aaaa|1111", and so on, represent the fields (Acct and Parcel) in each file that relate to the other files.

With the data contained in the files exactly as displayed above, my SQL selects have not been able to build result sets that produce a like display. For example,when I sucessfully join (acct=real=pers=flat) (row (1)), the join result for row (2) (acct=real=per) also selects row (1) again because it satisfies the where/join condition also. I have tried unions, various joins, where clause joins, etc. and connot get the desired results. I have created a very slow solution that does work by processing one account and parcel number combination at a time. This takes 4 hours to process. I need this data retreived for many different requirements from inquiry to bill calculations.

I've purchase SQL books and tried hundreds of experimentation/variations. As I said before, If you can help me out with a solution, you are a better man/woman than I, as Im out of ideas.

Thanks Gary.