>Contacts > | > -< Links > | > -< Phones > > > >The structures: > >Contacts >RecordId FName LName >----------------------------------------------------- >00000001 Joe Smith > >Links >RecordId ParentId DataId Key >----------------------------------------------------- >AAAAAAAA 00000001 ZZZZZZZZ "phone" >AAAAAAAA 00000001 QQQQQQQQ "phone" >AAAAAAAA 00000001 FFFFFFFF "phone" > > >Phones >RecordId Caption Phone Last >----------------------------------------------------- >ZZZZZZZZ Home 8005551212 .T. >QQQQQQQQ Business 8001212555 .T. >FFFFFFFF Cell 8885544877 .T. >
select Contacts.*, Phones.Caption, Phones.Phone ; from Contacts ; left join links on Contacts.RecordId = Links.ParentID ; and Links.Key == 'phone' ; left join Phones on Links.DataID = Phones.RecordIDHowever I suggest you to rethink about this design. Instead of holding multiple link info in a single table having them in separate files is better IMHO (and if it's not for supporting many-to-many completely remove links for these 2 tables).