Helen Borrie wrote:

>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.
>

Do you mean that the joins aren't being used for anything? The reason is
that I omitted the where clause, in which these are used. Obviously, I
need them, otherwise I wouldn't bother posting this question.

My point was that the joins weren't behaving correctly. Whether or not I
use the joins, they should still function correctly...

>And if ordre_fab.code is the primary
>key, you shouldn't be using DISTINCT.
>

Why not?

> Oh, and strings are delimited with
>single-quotes.
>
>

In dialect 1, which I'm using, that's not necessarily so. I know how to
formulate a string contant in SQL. I must say, your comments are really
quite insulting!...

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

No, this is not what I was trying to do at all. I know how to do that of
course. My point was that, when using the joins, which of course I need,
the behaviour is no longer correct.

Thanks,
Jonathan Neve.


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