Hi, Terry.

* You should use INNER JOIN to include only the records of the MINERS
table that have a correspondence with the records of the view.

* You should use LEFT OUTER JOIN (use the three words) to include all
the records of MINERS, including those that do not have a
correspondence with the records in the view.

Cheers,

Raúl

--- In firebird-support@yahoogroups.com, "Terry" <tchris@w...> 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.
>
> -Terry