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.

Any ideas?

Thanks!

Jonathan Neve.


[Non-text portions of this message have been removed]