Try

Select
TabMaster.ID,
(Select Detail from TabDetail where TabMaster.Detail1ID=TabDetail.ID) AS
Detail1,
(Select Detail from TabDetail where TabMaster.Detail2ID=TabDetail.ID) AS
Detail2,
(Select Detail from TabDetail where TabMaster.Detail3ID=TabDetail.ID) AS
Detail3
from tabMaster

-------------------------

----- Original Message -----
From: "Andreas Hesse" <ah@...>
To: "Florian Hector" <FHector@...>
Cc: "IBObjects" <ibobjects@yahoogroups.com>
Sent: Tuesday, June 08, 2004 11:41 AM
Subject: Re: [IBO] Several Joins on the same table for different fields


| 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
|
|
|
|
|
___________________________________________________________________________
| IB Objects - direct, complete, custom connectivity to Firebird or
InterBase
| without the need for BDE, ODBC or any other layer.
|
___________________________________________________________________________
| http://www.ibobjects.com - your IBO community resource for Tech Info
papers,
| keyword-searchable FAQ, community code contributions and more !
| Yahoo! Groups Links
|
|
|
|