At 09:19 AM 23-04-01 +0200, you wrote:

>Curious problem with interbase 6.0 :
>
>With IBConsole, this query :
>select Q3.c_name,Q3.c_phone,Q1.*
>from PRODUITFICHE Q1 ,PRODUITDESIREFICHE Q5
>left join CONTACTFICHE Q3 on Q1.p_codecontact=Q3.c_code
>where Q1.p_codemodele=Q5.pd_nomproduit
> and Q5.pd_codecontact="OUEST1354COB"
>order by Q1.P_NAME
>
>Return me :
>C_Name | C_PHONE | P_CODE | P_COST ..
>2ES | 016 016 155| 139 | 544,25 ..
>
>
>But :
>select Q3.c_name,Q3.c_phone,Q1.*
>from PRODUITFICHE Q1 ,Produitdesirefiche Q5
>left join CONTACTFICHE Q3 on Q1.p_codecontact=Q3.c_code
>where Q1.p_codemodele=Q5.pd_nomproduit
> and Q5.pd_codecontact="OUEST1354COB"
>order by Q1.P_NAME desc
>(Only order clause change)
>
>
>Returne me :
>C_Name | C_PHONE | P_CODE | P_COST ..
><Null> | <Null> | 139 | 544,25 ..
>
>Why C_name and c_phone are egual to <null> with the desc clause ?

Fabrice,
Curious query syntax sometimes produces curious results, if it doesn't actually generate an error....

You have here mixed implicit and expliciti join syntax and have produced ambiguity.

Use either ALL implicit (SQL-89) or all explicit (SQL-92) syntax. Explicit is preferred because you are not then mixing WHERE criteria up with JOIN criteria.

Try:

select Q3.c_name,Q3.c_phone,Q1.P_NAME, Q1....<whatever>
from PRODUITFICHE Q1
join PRODUITDESIREFICHE Q5
on Q1.p_codemodele=Q5.pd_nomproduit
left join CONTACTFICHE Q3 on Q1.p_codecontact=Q3.c_code
where Q5.pd_codecontact='OUEST1354COB' /* note SINGLE QUOTES!! */
and Q1.P_NAME is not null
order by Q1.P_NAME desc

Cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________