At 05:13 PM 27/02/2004 +0000, you wrote:

>I have a small table, MINERS with the following DDL:
>
>CREATE TABLE MINERS (
> ID INT_ID NOT NULL,
> CODE VC_15 NOT NULL,
> DESCRIPTION VC_50 NOT NULL
>);
>
>...and a large (~20000 records) table:
>
>CREATE TABLE SHIFT_DATA (
> ID INT_ID NOT NULL,
> SHIFT_DATE ADATE NOT NULL,
> SHIFT_YEAR INT_SM,
> SHIFT_MONTH INT_SM,
> FOREMAN_ID VC_15 NOT NULL,
> LEADMAN_ID VC_15 NOT NULL,
> CREW_COUNT INT_SM DEFAULT 0 NOT NULL,
> MAN_HOURS INT_SM DEFAULT 0 NOT NULL,
> SHIFT_ID INT_ID NOT NULL,
> CREW_ID INT_ID,
> MINER_ID INT_ID NOT NULL,
> SHIFT_LENGTH INT_SM DEFAULT 720,
> LOC_ID INT_ID NOT NULL,
> DIRECTION MINE_DIR NOT NULL,
> AVG_HEIGHT NUM_8_2 default 0.0 NOT NULL,
> FEET NUM_8_2 DEFAULT 0,
> TONS NUM_8_2 DEFAULT 0 NOT NULL,
> GAS_HOLE BOOL DEFAULT 0 NOT NULL,
> ROCK_HOLE BOOL DEFAULT 0 NOT NULL,
> COMMENT COMMENT,
> IMG_FILE FILE_PATH
>);
>
>
>MINER_ID is the foreign key linked to MINERS. I created the
>following view of SHIFT_DATA:
>
>CREATE VIEW REP_TONS_MONTH_M(
> YR,
> MO,
> MINER_ID,
> TONS)
>AS
>select extractyear(shift_date),
> extractmonth(shift_date),
> miner_id,
> sum(tons)
>from shift_data
>group by extractyear(shift_date), extractmonth(shift_date), miner_id;
>
>Note:(extractyear() and extractmonth() are UDFs)
>
>Now, the problem is that when I use this query:
>
>select m.code miner,
> sum(t.tons) tons
>from miners m
> left join rep_tons_month_m t on t.miner_id = m.id
>where t.yr = 2004
> and t.mo = 2
>group by m.code;
>
>...some of the records from MINERS don't appear! Isn't that the
>point of a LEFT JOIN? I need all of MINERS and the associated
>SUM()s from the view.
>
>Could someone give me some guidance here? Many thanks.

If there are any miners who had no shift records for 2004/2 the left join
returns a row to the intermediate (pre-grouping) set, containing m.code
with nulls for t.tons, t.yr and t.mo. So far, so good.

But the sum() aggregation ignores rows with null in t.tons, so there is
nothing to output from the aggregation for those miners.

You'll need to find a way to get a value (0) into the picture before the
aggregation occurs. Exactly how you do that depends on the database
dialect and the server version you are using.

/heLen