Hi HTH

who ever you are, thank you it works !
Bit how do I get blank instead of NULL in the result table ?

Regards
Philipp

2010/9/1 Svein Erling Tysv�r <svein.erling.tysvaer@...>

>
>
> Two things:
>
> 1) Don't mix SQL-89 (implicit JOIN) and SQL-92 (explicit JOIN).
> 2) Referring to KO.TYP in the WHERE clause in reality changes the left join
> to an inner join.
>
> So, assuming you really want a left join, change to:
>
> SELECT DISTINCT
> PR.SUCHBEGRIFF as Liegenschaft,
> AD.VORNAME,
> AD.NAME1,
> KO.NUMMER,
> KO.TYP
> FROM ADRESSE ad
> JOIN ADRESSE_ADRESSKATEGORIE aa on AA.ADRESSE_ID = AD.ID
> JOIN ADRESSKATEGORIE ak on AK.ID = AA.ADRESSKATEGORIE_ID
> JOIN VERTEILEREINTRAG ve on VE.ADRESSE_ID = AD.ID
> JOIN PROJEKT pr on VE.VERTEILER_ID = PR.BETEILIGTEVERTEILER_ID
> left join KOMMUNIKATION ko on AD.ID = KO.ADRESSE_ID and KO.TYP = 'Privat'
> WHERE
> AK.SUCHBEGRIFF = 'Hauswart'
>
> And see if that eliminates your problem.
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>[mailto:
> firebird-support@yahoogroups.com <firebird-support%40yahoogroups.com>] On
> Behalf Of Philipp Franzos
> Sent: 1. september 2010 10:42
> To: firebird-support@yahoogroups.com <firebird-support%40yahoogroups.com>
> Subject: Re: [firebird-support] Firebirs 2.1 : left join right join full
> join outer join dont' work !
>
> Hi Helen
>
> Thanks for trying to help. Here the exact SQL:
>
> SELECT DISTINCT
> PR.SUCHBEGRIFF as Liegenschaft,
> AD.VORNAME,
> AD.NAME1,
> KO.NUMMER,
> KO.TYP
> FROM
> ADRESSE ad,
> ADRESSKATEGORIE ak,
> ADRESSE_ADRESSKATEGORIE aa,
> VERTEILEREINTRAG ve,
> PROJEKT pr
> left outer join KOMMUNIKATION ko on AD.ID = KO.ADRESSE_ID
> WHERE
> AK.SUCHBEGRIFF = 'Hauswart' AND
> AK.ID = AA.ADRESSKATEGORIE_ID AND
> AA.ADRESSE_ID = AD.ID AND
> VE.ADRESSE_ID = AD.ID AND
> VE.VERTEILER_ID = PR.BETEILIGTEVERTEILER_ID AND
> KO.TYP = 'Privat'
>
> Kind Regards
> Philipp
>
>
>

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