OUTER JOINs Not Working Correctly

OUTER JOINs Not Working Correctly

Post by Craig Bryde » Fri, 15 Feb 2002 17:09:52



Hi

I am having a very strange problem with SQL 2000. I write a simple LEFT
OUTER JOIN query, and it returns only the records where there are matching
records on both tables (no different from an INNER JOIN).

Below is an example of a query that I have run:
select a.movieno, b.movieno
from lst_movies a
        left outer join lst_fcstMaster b
        on a.movieno = b.movieno
where b.fcst_number = 12

the table lst_movies contains approximately 7000 records. The lst_fcstmaster
table contains only 65 (where the field fcst_number = 12).
The result set is only 65 records and not 7000.

Thanks for any help

Craig

 
 
 

OUTER JOINs Not Working Correctly

Post by Tony Rogerso » Fri, 15 Feb 2002 17:06:52


PS... Check out the kbase on the site below in tutorials, I've a
presentation i did ages ago on moving from old style to ansi 92 and covers
weird stuff like this.

Tony.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]


Quote:> Hi

> I am having a very strange problem with SQL 2000. I write a simple LEFT
> OUTER JOIN query, and it returns only the records where there are matching
> records on both tables (no different from an INNER JOIN).

> Below is an example of a query that I have run:
> select a.movieno, b.movieno
> from lst_movies a
>         left outer join lst_fcstMaster b
>         on a.movieno = b.movieno
> where b.fcst_number = 12

> the table lst_movies contains approximately 7000 records. The
lst_fcstmaster
> table contains only 65 (where the field fcst_number = 12).
> The result set is only 65 records and not 7000.

> Thanks for any help

> Craig


 
 
 

OUTER JOINs Not Working Correctly

Post by Tony Rogerso » Fri, 15 Feb 2002 17:06:09


It's because your filter condition is on the where clause, this will convert
the outer join to an inner join.

You need to put the filter in the ON clause...

Quote:> select a.movieno, b.movieno
> from lst_movies a
>         left outer join lst_fcstMaster b
>         on a.movieno = b.movieno
>           and b.fcst_number = 12

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]


Quote:> Hi

> I am having a very strange problem with SQL 2000. I write a simple LEFT
> OUTER JOIN query, and it returns only the records where there are matching
> records on both tables (no different from an INNER JOIN).

> Below is an example of a query that I have run:
> select a.movieno, b.movieno
> from lst_movies a
>         left outer join lst_fcstMaster b
>         on a.movieno = b.movieno
> where b.fcst_number = 12

> the table lst_movies contains approximately 7000 records. The
lst_fcstmaster
> table contains only 65 (where the field fcst_number = 12).
> The result set is only 65 records and not 7000.

> Thanks for any help

> Craig

 
 
 

OUTER JOINs Not Working Correctly

Post by Mo Lin[M » Wed, 20 Feb 2002 14:36:22


Hi Craig,

The correct syntax should be:

select a.movieno, b.movieno from lst_movies a
        left outer join lst_fcstMaster b
        on a.movieno = b.movieno
       and b.fcst_number = 12

Using "Where" clause will return a different result that you don't want.
You can analyze the query plan to get more details for this issue.

This posting is provided "AS IS" with no warranties, and confers no rights

Regards,
Mo Lin
Microsoft Support Engineer

 
 
 

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

2. Crystal Reports - Displaying selection data in report

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

4. US-CA-DBA

5. This outer join is not working right -why?

6. ADO Recordset Filter

7. Outer join filter from derived table not working

8. .FIL => .DDF converter

9. upgraded from 6.0 to 7.0 and Outer Join not working

10. LEFT OUTER JOIN not working as expected

11. left outer join not working with certain ODBC drivers

12. Plz Help with OUTER JOIN Query-Not Working

13. Full Outer Join Not Working