My mistake. I think you are already using a FULL JOIN and not getting the results.

What IDs will the invc.VJobID and chck.CJobID contain?


>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