>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

First example uses inner join. If Outer not specified then by default it returns matches only. The second UNION example by default eliminates duplicate ID's from the results.