[SQL]: GROUP BY Problems

[Frank Schima]

Hi Ruslan,


I am encountering GROUP BY problems with V4RB 1.9.8b8.

Here is my SQL:
SELECT t.Type, count(*) FROM Types t, Patterns p WHERE t.RecID *= p.TypePtr GROUP BY 
t.Type

A simple outer join. The problem is that the Count(*) is wrong for the Types that should 
have 0 - instead it says 1.

So I get:
Type A, 4
Type B, 1
Type C, 1

But I should get:
Type A, 4
Type B, 0
Type C, 0

There are only 4 records in the Patterns table! The inner join version of the SQL (i.e. no 
WHERE clause) correctly returns one record:
Type A, 4

Another problem occurs if I try to also select the RecID, then it does not work correctly. I 
brought this up before but I don't remember the resolution. Maybe it was to wait for 
version 2 of the kernel?

Let me know if you want my database off-list.


Best regards,
Frank

---
Frank Schima                <mailto:frank-list2 at-sign mindstarprods.com>
Independent Consultant
Gaithersburg, MD USA        <http://www.ci.gaithersburg.md.us/>