>Hello,
>
>I hope the title says more or less what I would like to achieve. For a better overview I've made two screenshots.
>
>That's the given structure (3 tables):
>http://www.pic-upload.de/view-24012442/01_given_structure.png.html
>
>
>And that's what I would like to have as result, to be able to select from it:
>http://www.pic-upload.de/view-24012443/02_result.png.html
>
>I'm at the point to think that it only can be achieved by a stored procedure (if at all), but somehow I struggle to figure out, how to do it with 3 tables...
>
>Any hint into the right direction would be highly appreciated.
>

Hi Patrick!

This is easy:

select c.ID, c.NAME1, c.NAME2, c.ADDRID1, c.ADDRID2, c.ADDRID3, c.PERSID1, c.PERSID2, c.PERSID3,
a1.STREET STREET1, a2.STREET STREET2, a3.STREET STREET3,
p1.PNAME PERSON1, p2.PNAME PERSON2, p3.PNAME PERSON3
from CONTACTS c
left join ADDRESSES a1 on c.ADDRID1 = a1.ID
left join ADDRESSES a2 on c.ADDRID2 = a2.ID
left join ADDRESSES a2 on c.ADDRID3 = a3.ID
left join PERSONS p1 on c.PERSID1 = p1.ID
left join PERSONS p2 on c.PERSID1 = p2.ID
left join PERSONS p3 on c.PERSID1 = p3.ID

(replace a1.STREET with COALESCE(a1.STREET, '') if you want an empty string and not null in your result set).

I don't understand why you have CONTACTID as a field in ADDRESSES and PERSONS. There could be good reasons, but for this particular question, that field is redundant.

HTH,
Set