>Hi Jim,
>
>I'm not sure why do you need additional table if this info is already stored in your tables?
>The following query (not tested) should give you releated loans

>SELECT Loan.* FROM Loan
>	  JOIN Property p1 ON loan.id = p1.loan_id
>	  JOIN Property p2 ON p2.id = p1.id AND p2.loan_id = lnLoanID
>	WHERE Loan.id <> lnLoanID				  	
>

Hi Sergey,

What it looks like to me is that the loan FK should be removed from the Property table, given that the ownership table now applies potentially N loans to 1 property. Ownership seems a bit of a misnomer here, it is more like a list of loans on the property.

Having worked for years with a poorly normalized legacy database, I am rusty. <g> I might propose this situation below, and then a variation of what you propose might work, by passing through the ownership table.


loan table:

  Id I Not Null Autoinc Nextvalue 1 Step 1, ;
	LOANACCT C(20) Not Null, ;
	DISPLAY_ACCT C(20) Not Null, ;

&& and others specific to a given loan ...

Property table

'PROPERTY.DBF
 ID I NOT NULL AUTOINC NEXTVALUE 18803 STEP 1, ;
 ORDER_DATE D NOT NULL, ;
&& others specific to a given property

Ownership table

   ID I NOT NULL AUTOINC NEXTVALUE 1 STEP 1 &&- PK 
   PROP_ID I NOT NULL, ;  && fk - PROPERTY
   LOAN_ID I NOT NULL, ;  && fk - LOAN
   PCNT N(7, 6) NOT NULL ;