[SQL]: GROUP BY Problems

[Ruslan Zasukhin]

on 6/13/03 20:54, Frank Schima at frank-list2 at mindstarprods.com wrote:

Hi Frank,

> 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

So what?
But count(*) counts records in the JOIN table, correct?

Frank, please test this query on other DBMS,
It seems to me result is correct.

> 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?

    SELECT RecID, t.Type, count(*)      ?

But this is not correct, because RecID is not under GROUP BY.
Correct?    

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

-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------