Hi,

I have quite a strange problem using left outer joins.
If I start the query without a where clause the query returns the correct
number of records.

If I add a where clause querying the joined table the query returns more
records than the query without a where clause (the where works correctly but
the join does not!)

following the two queries:

SELECT  PF_AdrTel.AT_TelNr1,
    PF_Person.*,
    PF_Strassen.*,
    PF_Empfaenger.*,
    PF_AdrTel.*
FROM PF_Person LEFT JOIN PF_Strassen ON PF_Person.JP_AS_Nummer =
PF_Strassen.AS_Nummer
               LEFT JOIN PF_Empfaenger ON PF_Strassen.AS_EM_Nummer =
PF_Empfaenger.EM_Nummer
               LEFT JOIN PF_AdrTel ON PF_Empfaenger.EM_AT_Nummer =
PF_AdrTel.AT_Count

SELECT  PF_AdrTel.AT_TelNr1,
    PF_Person.*,
    PF_Strassen.*,
    PF_Empfaenger.*,
    PF_AdrTel.*
FROM PF_Person  LEFT JOIN PF_Strassen ON PF_Person.JP_AS_Nummer =
PF_Strassen.AS_Nummer
         LEFT JOIN PF_Empfaenger ON PF_Strassen.AS_EM_Nummer =
PF_Empfaenger.EM_Nummer
         LEFT JOIN PF_AdrTel ON PF_Empfaenger.EM_AT_Nummer =
PF_AdrTel.AT_Count
WHERE PF_Empfaenger.EM_GebDat >= '01/1/72' And PF_Empfaenger.EM_GebDat <=
'12/12/72'

Has anybody experienced the same behaviour?
Do I anything wrong?

thanks for your help,

Marcus Tainschek
Internet & Development
Saltware EDV GmbH & Co KG

Marcus,

Very strange.  What service pack are you running?  Many problems with
the new join syntax were fixed in the service packs.

Roy

Hi Roy,

I am running SQL-Server 6.5 with service pack 3. It is the first time I saw
such a strange behaviour, so I am quite worried.

Marcus