>I have two tables: checks and invoices (don't we all?):
>TABLE INVC (VNO I, VJOBID C(10), VAMT Y)
>TABLE CHCK (CNO I, CJOBID C(10), CAMT Y)
>
>I want to compair total billed vs total paid for each JobID.
>
>The following dosn't work:
>
>SELECT VJOBID, SUM( VAMT ), SUM( CAMT ) ;
> FROM INVC JOIN CHCK ON VJOBID = CJOBID ;
> GROUP BY VJOBID
>
>because of the many to many relationship between the tables.
>
>I can do
>
>SELECT VJOBID, SUM( VAMT ) ;
> FROM INVC ;
> GROUP BY VJOBID
>UNION ;
>SELECT KJOBID, SUM( CAMT ) ;
> FROM CHCK
> GROUP BY KJOBID
>
>but I would like to be able to define a view that can be exported via ODBC so that a user can create crystal report reports. (now we know the real problem!)
>
>^Carl Karsten

I would propose a three step solution. You've already found out the first example does not work, I think neither your second statement does, because of the loss of duplicate ID's / column, so:

Select vjobid, sum(vant) as vant From invc Group By vjobid Into Cursor first
Select kjobid, sum(camt) as camt From chck Group By kjobid Into Cursor second
Select a.vjobid, a.vant, b.camt From first a ;
  Left Outer Join second b On a.vjobid=b.kjobid

Of course if you're running this against an ODBC data source it gets tricky using three remote views or, if SQL pass-through, use the third parameter of the SQLExec to name your cursors.