John,

The LEFT JOIN includes all records from the 'line' table into result set even if there're no corresponded records in the 'rolls' table. Howhever, WHERE clause kills those records. Change it to

WHERE UPPER(ALLTRIM(CSTATUS))=="ACCEPTED" OR CSTATUS IS NULL ;

>I am uing the following SQL statement:

>SELECT LINE.cCode, SUM(ROLLS.nWeight_Roll) AS nAccepted ;
>FROM LINE ;
>LEFT JOIN ROLLS ;
>ON LINE.ID=ROLLS.clineid ;
>WHERE UPPER(ALLTRIM(CSTATUS))=="ACCEPTED" ;
>GROUP BY LINE.cCode ;
>INTO CURSOR tmpProduction

>
>In my LINE table, I have 11 records. cCode={101,102,103....,111}.
>ROLLS table is the rolls produced on a particulare line/machine-line.
>
>I would like to have 11 records after my SQL statement executes in the temporary cursor, however I only get 5. This is because 6 of the lines have no ROLL records associated with them since no production was ever performed on those lines yet.
>
>How can I request that all lines are given, even if the SUM returns 0.00?
>
>Thanks in advance,