Left /Right ?Join Grouping Problem

[databuilt]

I am trying to create a report where I am grouping sales data by a sales rep for a special promotion items. Under the sales rep, I want to show the complete list of promo items, even though the rep may not had had any sales. Here is sample of the query that returns the sales but when I group by rep in the report I can only get a list of items sold:

SELECT
drdl01 category
, imlitm as item
, imdsc1 as desc1
, imsrp6 as promo
, promoname='Sticker Catalog Promotions (CAT03-3)'
, sum(sdaexp)/100 sales
, sdslm2 rep

from proddta.f4211
right join proddta.f4101
on sdlitm=imlitm and imsrp6='CAT033'
inner join prodctl.f0005
on drsy=41 and drrt='S2' and ltrim(drky)=imsrp2


where
sdtrdj>=103085 and sdtrdj<=103090 -- booking period
and not sdlnty in ('F','M','RS')
and sddct='RI'
and sdsocn=0
and sdktln=0


group by
drdl01
, imlitm
, imdsc1
, imsrp6
, sdslm2

 

[swampBoogie]

Probably a column from the proddta.f4211 table is present in the where clause.

 

[databuilt]

Yes.. there are a few. I have tried left joins like the following but I run into the same problem. When I group (in the report) by the rep, I only get rows where the rep has sales. I also want to include rows where the rep does not have sales:

SELECT
drdl01 category
, imlitm as item
, imdsc1 as desc1
, imsrp6 as promo
, promoname='Sticker Catalog Promotions (CAT03-3)'
, sum(sdaexp)/100 sales
, sdslm2 rep

from proddta.f4101
left join proddta.f4211
on sdlitm=imlitm
and sdtrdj>=103085 and sdtrdj<=103090 -- booking period
and not sdlnty in ('F','M','RS')
and sddct='RI'
and sdsocn=0
and sdktln=0
inner join prodctl.f0005
on drsy=41 and drrt='S2' and ltrim(drky)=imsrp2


where
imsrp6='CAT033'

group by
drdl01
, imlitm
, imdsc1
, imsrp6
, sdslm2

 

[swampBoogie]

Put all search condition for proddta.f4211 and prodctl.f0005 in the on clauses and you must also use

left join prodctl.f0005

instead of an inner join.

 

[databuilt]

Thanks swampBoogie.

I am a little confused. In the example I provided above, all the search conditions for f4211 and f0005 are in the FROM clause. Should they be in the WHERE clause? I'll make the change on the inner join (to left).

 

[databuilt]

That doesn't do it. What I am looking for as a result would look something like this

Sales Rep Promo Category Sales

Jim
Stickers
Cat1 0
Cat2 100
Cat3 50
Cat4 0

Sally
Stickers
Cat1 50
Cat2 0
Cat3 0
Cat4 0

What I get now is only those categories where the rep has sales. Do I need to use some sort of WHERE sdslm2 IS NULL statement? For each sales rep I want the complete list of categories.