LEFT OUTER JOIN not working?

[fmgenius]

This looks simple and correct to me yet in this case, FM will not return the null value for a value A6 on the left. 

Here is the example SQL

SELECT 
	"sach"."AchievementNumber",  
	COUNT("searned"."_scout") 
	
FROM "sach" 
	LEFT OUTER JOIN "searned" 
	ON "sach"."_id" = "searned"."_achievement" 		
	
WHERE 
	"searned"."_scout" = ? 
	and "isRegularAchivement" = 1  		
	
GROUP BY "sach"."AchievementNumber" 		
ORDER BY "sach"."AchievementNumber" 		

 

I should get 12 rows every time, yet this is all I get:

 

A1,6

A2,8

A3,3

A4,7

A5,10

A7,4

A8,2

A9,5

A10,3

A11,4

A12,5

 

if I run this

SELECT DISTINCT( ''||"AchievementNumber" ), "requiredAchievements" FROM "sach" WHERE "isRegularAchivement" = ? ORDER BY "AchievementNumber"

 

 

A1,6

A2,7

A3,3

A4,6

A5,5

A6,3

A7,6

A8,5

A9,5

A10,3

A11,4

A12,5

 

Why won't FM properly return a null for the missing rows for A6?

 

[beverly]

That's what the OUTER JOIN does. Think of it as parent records with child portal rows. The parents will all be returned even if the portal is empty (no related children). However, if you make implied JOIN, does it return 0 count on empty children?

FROM sach, searned

WHERE sach.id = searned."_achievement"

COUNT() is looking for NOT NULL, so I doubt you'd get what you want that way as well. Perhaps you need a CASE to test and return 0 when there are no children?