SQL Query - INNER JOIN - Please Help

[Chris100682]

i, I need to link a table to my below query using an INNER JOIN. The table I need to join is called (CIT SPOT) and the field I'm joining is Src_prod_ofr_no (as this is in both CIT_SPOT and SRC_PROD_OFR tables). Can someone show me how to write this to my query below? Thanks

select source_code, max(eff_dt) eff_dt
from prod_ofr_fg
where isu_no="31"
group by 1
into temp target1 with no log;

select a.source_code, a.fee_grp_no, a.eff_dt
from prod_ofr_fg a, target1 b
where a.source_code = b.source_code
and a.eff_dt = b.eff_dt
into temp prod_ofr_fg_dd with no log;

select a.source_code, a.product_type,
a.gip, a.primary_acq_type, a.group_code, a.program_name,
a.secondary_acq_type, b.fee_grp_no
from source_codes a, prod_ofr_fg_dd b
where a.group_code in
("TEST")
and a.source_code=b.source_code
into temp codes with no log;

unload to TEST_data.txt
select c.src_prod_ofr_no, a.source_code, c.prod_ofr_cd, d.promo_type, d.promo_desc, a.fee_grp_no,
f.group_code, f.group_name, e.primary_acq_name,
d.roll_ind, d.roll_duration, c.add_dt
from codes a, ssba_contracts b, src_prod_ofr c
promotions d, prim_acq e, groupinfo f
where a.source_code=c.source_code
and b.contract_id=c.contract_id
and c.promo_id=d.promo_id
and a.primary_acq_type=e.primary_acq_type
and a.group_code=f.group_code
and c.sts_cd="A"
and a.primary_acq_type not in
("4","Q")
and a.source_code not like "0%"
and c.add_dt <= '15/09/2005'

[OBonio]

...
from codes a, ssba_contracts b, src_prod_ofr c
promotions d, prim_acq e, groupinfo f
INNER JOIN CIT_SPOT ON CIT_SPOT.Src_prod_ofr_no = SRC_PROD_OFR.Src_prod_ofr_no
where a.source_code=c.source_code
...

You should really be using JOINs to hook all those tables together. Would really tidy up that WHERE clause...

[Chris100682]

Obonio - As ever - first class!

[Chris100682]

Thanks for the advice Obonio, I've inserted this into my query but it keeps coming back with

522: Table (src_prod_ofr) not selected in query.

Can you help?

[OBonio]

Hm, it's the alias... it should be :

INNER JOIN CIT_SPOT ON CIT_SPOT.Src_prod_ofr_no = c.Src_prod_ofr_no