Florian Hector schrieb:

>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?
>
>
>Florian
>
>
>

you should use a join with 4 tables. If like in your example one detail
is empty you have to use a "left outer join":

SELECT ID, a.Detail AS Detail1, b.Detail AS Detail2, c.Detail AS Detail3
FROM tabMaster
LEFT OUTER JOIN TabDetail a on TabMaster.Detail1ID=a.ID
LEFT OUTER JOIN TabDetail b on TabMaster.Detail2ID=b.ID
LEFT OUTER JOIN TabDetail c on TabMaster.Detail3ID=c.ID

This should be the correct join.

Andreas Hesse