Why does WHERE Clause affect Left Outer Join Results?

[jbailey268]

I have a query that pulls from 2 Free tables
AK_SPECS.DBF and SPECIALTY.DBF
The first is Physicians in Alaska
The second is the universe of doctor specialties.
My objective is to have all 213 specialties appear in the output with the counts of prescribers per specialty.

SELECT COUNT(AK_SPECS.PPRIMSPEC) as spc_cnt,SPECIALTY.CODE ;
FROM SPECIALTY LEFT OUTER JOIN AK_SPECS ;
ON SPECIALTY.CODE = AK_SPECS.PPRIMSPEC ;
GROUP BY 2 Into Cursor Temp && yields 213 rows with the respective counts per specialty.
This is good !

After I put a where clause filter in...

SELECT COUNT(AK_SPECS.PPRIMSPEC) as spc_cnt,SPECIALTY.CODE ;
FROM SPECIALTY LEFT OUTER JOIN AK_SPECS ;
ON SPECIALTY.CODE = AK_SPECS.PPRIMSPEC ;
WHERE AK_SPECS.ACTIVE = "Y" ;
GROUP BY 2 Into Cursor Temp && yields 48 rows
?? WHY NOT 213 Rows ??

The only differrence I see is the "WHERE" clause.
The only thing remaining to do to get the results I want is to first select a subset of records from the master (AK_SPECS) based on the where clause and with this table as the new universe run the original query (with no where clause) and I get the desired result. How Counter-Intuitive and clumsy is that?
Can it be done all at once?

Does it have anything to do with ANSI or EXACT settings or anything else.
These are free tables.
It's VFP 8
The Engine behavior is 8 (but I have tried 7 with equally unsuccessful results)
Thank you in advance for your help.

 

[bborissov]

SELECT COUNT(AK_SPECS.PPRIMSPEC) as spc_cnt,;
       SPECIALTY.CODE   ;
FROM SPECIALTY;
LEFT OUTER JOIN AK_SPECS ON SPECIALTY.CODE = AK_SPECS.PPRIMSPEC ;
WHERE AK_SPECS.ACTIVE = "Y" ;
GROUP BY 2 Into Cursor Temp && yields 48 rows

What you expect? You said to query to pull ONLY these records that have AK_SPECS.ACTIVE = "Y". That not means give me all records from SPECIALTY and only these from AK_SPECS that has Active=[Y], That means give me ONLY these records from SPECIALTY wich have corespondinng records in AK_SPECS and AK_SPECS.Active = [Y]. If you want To get ALL records from SPECIALTY and only these records from AK_SPECS that has Active = [Y] change the query:

SELECT COUNT(AK_SPECS.PPRIMSPEC) as spc_cnt,;
       SPECIALTY.CODE   ;
FROM SPECIALTY;
LEFT OUTER JOIN AK_SPECS ON SPECIALTY.CODE = AK_SPECS.PPRIMSPEC AND AK_SPECS.ACTIVE = "Y" ;
GROUP BY 2

Borislav Borissov

 

[jbailey268]

Thank you Borislav; it worked.
It was a subtlety that escaped me. Don't know why it didn't occur to me to just try a simple "AND" rather than WHERE.
I guess "Where" supercedes the outer join - as it should. Thank you again. You saved me a step in the code and I like clean and consise code.

Much appreciated.