OUTER JOIN restricted 2

[MBaraz]

I thought this was going to be simple... and I'll bet it is if you know the answer...

I am trying to create a list of all possible items from reference table (ProdPriceClass) and show the discounts for each one from other table (ProdUserGroupPriceClass); if there in no matching row in ProdUserGroupPriceClass (right table). This is a classic outer join. However I only want the ProdUserGroupPriceClass for a specific Group_ID and that's where things go wrong...

Table: ProdUserGroupPriceClass
ProdPriceClass_ID       int,
Group_ID                int,
DiscountValue           double

Data:
1   1    0.1
2   1    0.15
1   3    0.02
2   3    0.05
3   3    0.06
5   3    0.08
6   3    0.1
7   3    0.15


Table: ProdPriceClass
ProdPriceClass_ID       int,
Name                    varchar(50)

Data:
1             Full price
2             Very low counts
3             Low discounts
4             Standard
5             Competitive
6             Very competive
7             Loss leaders

Here's what I thought would work:

SELECT ProdPriceClass.*, DiscountValue 
FROM ProdPriceClass LEFT OUTER JOIN ProdUserGroupPriceClass
	ON ProdPriceClass.ProdPriceClass_ID = ProdUserGroupPriceClass.ProdPriceClass_ID
WHERE ProdUserGroupPriceClass.Group_ID = 3 

Output (missing ProdPriceClass_ID = 4):

ProdPriceClass_ID Name                        DiscountValue
------------- ------------------------------------ -------- 
1             Full price                           0.02
2             Very low counts                      0.05
3             Low discounts                        0.06
5             Competitive                          0.08
6             Very competive                       0.1
7             Loss leaders                         0.15
 
6 Row(s) affected

So, I am missing the complete list of ProdPriceClass_IDs but if I remove the WHERE then I get repeating values for ProdPriceClass_ID because there are several Group_IDs.

A little help please! --
Michael Baraz
Building data-driven, high-performance, massively-scaleable, infinitely extensible, über
websites

 

[rac2]

SELECT ProdPriceClass.*, DiscountValue 
FROM ProdPriceClass LEFT OUTER JOIN

(SELECT * FROM ProdUserGroupPriceClass
WHERE ProdUserGroupPriceClass.Group_ID = 3 ) ClassyDiscounts

ON ProdPriceClass.ProdPriceClass_ID = ClassyDiscounts.ProdPriceClass_ID

 

[MBaraz]

Excellent... thanks so much! --
Michael Baraz
Building data-driven, high-performance, massively-scaleable, infinitely extensible, über
websites