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