At 03:36 PM 31/03/2004 +0200, you wrote:

>Hi!
>
>I've got trouble with the following query (IB 6.0) :
>
>SELECT distinct o.code, o.date_ordre, o.etabli, o.cuve, o.chef_fab,
>o.prev, o.date_charg, o.type_of
>FROM ordre_fab o
>left outer JOIN ordre_fabcorps ofc on ofc.code = o.code and o.type_of = "PX"
>left outer JOIN ordre_fabcorps_sf sfc on (sfc.code = o.code and
>o.type_of = "SF")
>ORDER BY o.date_ordre
>
>ordre_fabcorps and ordre_fabcorps_sf are two detail tables of ordre_fab.
>If the type_of field of ordre_fab is 'PX', we use the ordre_fabcorps,
>otherwise, we use ordre_fabcorps_sf. So in this query, I select a set of
>records from ordre_fab, and I want to join on the associated table,
>which may be either ordre_fabcorps or ordre_fabcorps_sf, depending on
>the value of type_of.
>
>My problem is that this doesn't behave the way I would expect.
>
> * If I execute the above statement, I get only the records where
> type_of = 'SF'.
> * If I invert the two joins, I get only the records where type_of =
> 'PX'.
> * If I remove either of the two joins, I get all the records.

..but you are not joining anything. And if ordre_fab.code is the primary
key, you shouldn't be using DISTINCT. Oh, and strings are delimited with
single-quotes.

SELECT
o.code,
o.date_ordre,
o.etabli,
o.cuve,
o.chef_fab,
o.prev,
o.date_charg,
o.type_of
FROM ordre_fab o
/* and then */
where o.type_of in ('PX', 'SF') /*gets both */
/*or */
where o.type_of = ('PX') /*gets one type */
ORDER BY o.date_ordre

/hb