At 05:56 PM 8/06/2004 +0200, you wrote:

>I have the following two tables:
>
>TabMaster
>ID Detail1ID Detail2ID Detail3ID
>1 1 3
>2 2 3 1
>3 3 3 2
>4 1 2 3
>5 3 1
>
>Detail 1,2 and 3 point to the same detailtable
>
>TabDetail
>ID Detail
>1 1000
>2 2000
>3 3000
>
>What I want to have is this:
>
>ID Detail1 Detail2 Detail3
>1 1000 3000
>2 2000 3000 1000
>3 3000 3000 2000
>4 1000 2000 3000
>5 3000 1000
>
>My Statement looks like this:
>
>Select
> ID,
> TabDetail.Detail AS Detail1,
> TabDetail.Detail AS Detail2,
> TabDetail.Detail AS Detail3
>from tabMaster
>Left Join TabDetail on TabMaster.Detail1ID=TabDetail.ID
>Left Join TabDetail on TabMaster.Detail2ID=TabDetail.ID
>Left Join TabDetail on TabMaster.Detail3ID=TabDetail.ID
>
>As long as I only query for the first Detail, it works as expected. When
>the first, second and third
>detail are queried, the resultset is completetly erratic, like all of a
>sudden all details are 1000.
>
>Is it at all possible what I want to do here?

First, Florian, please don't use the IBO list for your SQL problems. This
list is for IBO questions. Use firebird-support for non-IBO problems.

Second, I recommend investing in a good SQL book. There is a listing in
the Bookshop page of the main IBO website.

Last, just to get you on the right track, I offer this briefly as the
correct way to do re-entrant joins in SQL:

Select
m.ID,
td1.Detail AS Detail1,
td2.Detail AS Detail2,
td3.Detail AS Detail3
from tabMaster m
Left Join TabDetail td1 on m.Detail1ID=td1.ID
Left Join TabDetail td2 on m.Detail2ID=td2.ID
Left Join TabDetail td3 on m.Detail3ID=td3.ID

Helen