JOIN SQL statement with conditional WHERE clause without using procedures?

I’m using SQLServer 2005. I have a form where a user is selecting “Add-on” items. They can be in a specific plan which pays a different price for these add-ons than the retail cost.

I have two tables:

AddOns

ID--------AddonName--------RetailCost
6---------Red Tint---------85
7---------Green Tint-------75

Addons_Override (This table overrides the retail cost of the item with a special cost for that PlanID)

AddonID--------PlanID--------Cost
6--------------10------------50
6--------------11------------25

U开发者_开发百科ser chooses Red Tint (ID=6) and Green Tint (ID=7) and is in PlanID=10. I want to show these Addon items that they choose with the corresponding cost.

Want this result:

ID--------AddonName--------RetailCost--------Cost--------PlanID
6---------Red Tint---------85----------------50----------10
7---------Green Tint-------75----------------75----------NULL

Not this result:

ID--------AddonName--------RetailCost--------Cost--------PlanID
6---------Red Tint---------85----------------50----------10
6---------Red Tint---------85----------------25----------11
7---------Green Tint-------75----------------75----------NULL

Current SQL

SELECT 
  DISTINCT t1.ID, 
  t1.RetailCost, 
  Cost=case when t2.VisionPlanID=10 then t2.Cost else t1.RetailCost end,  
  t2.PlanID 
FROM 
  Addons t1 
LEFT OUTER JOIN 
  Addons_Override t2 
ON 
  t1.ID=t2.AddonID 
WHERE (VisionPlanID=10 AND ID=6) or ID=7

The problem is that I can’t write the select statement like this since I don’t know which ones are in the Addons_Override table. What select statement will work to give me the result I want above?

Please help!

SELECT t1.ID, t1.RetailCost,
CASE 
 WHEN t2.PlanID IS NOT NULL THEN t2.Cost
 ELSE t1.RetailCost
END as Cost,
t2.PlanID
FROM Addons t1
LEFT JOIN Addons_Override t2 ON (t1.ID = t2.AddonID AND t2.PlanID = 10)

SELECT t1.ID, t1.RetailCost,
    COALESCE(t2.Cost, t1.RetailCost) As Cost,
    t2.PlanID
FROM Addons t1
LEFT JOIN Addons_Override t2 ON t1.ID = t2.AddonID AND t2.PlanID=10
WHERE t1.ID IN (6,7)

SELECT 
  DISTINCT t1.ID, 
  t1.RetailCost, 
  Cost=case when t2.VisionPlanID=10 then t2.Cost else t1.RetailCost end,  
  t2.PlanID 
FROM Addons t1 
LEFT OUTER JOIN Addons_Override t2 ON  t1.ID=t2.AddonID AND
( (VisionPlanID=10 AND ID=6) OR ID=7)

Simply move any element that references table 2 from the WHERE clause to the JOIN clause.