left outer joins with a whereclause seem to not work correctly 
Author Message
 left outer joins with a whereclause seem to not work correctly

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



Mon, 31 Jul 2000 03:00:00 GMT
 left outer joins with a whereclause seem to not work correctly

Marcus,

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

Roy



Tue, 01 Aug 2000 03:00:00 GMT
 left outer joins with a whereclause seem to not work correctly

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


Quote:
>Marcus,

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

>Roy



Fri, 04 Aug 2000 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Left outer join works, Right outer join just goes on for ever, processor at 100%

2. LEFT OUTER LOOP JOIN and LEFT OUTER JOIN

3. OUTER JOINs Not Working Correctly

4. LEFT OUTER JOIN not working as expected

5. left outer join not working with certain ODBC drivers

6. LEFT JOIN and LEFT OUTER JOIN

7. LEFT JOIN and LEFT OUTER JOIN

8. Left-Outer join and Right-Outer join

9. Stored Procedure Query Problem - Right Outer Join don't seems to work

10. dts export to fixed field not working correctly or so it seems

11. HOW TO LEFT OUTER JOIN QUERY (NOT ANSWERED)

12. Left Outer Joins not noticing tables or aliases


 
Powered by phpBB® Forum Software