Join to Specific and Generic Entry in Table

[Auguy]

I have two tables in SQL 2000, Orders contains customer order information. CustSurcharge contains the new fuel rate surcharges for the coming week. For simplification the CustSurcharge table has two fields, a Cust ID and a New Rate. There are a few customers in the CustSurcharge table that have their own rates. I also have a generic Cust ID ("******") with the rate for all of the other customers. All orders not yet filled will get the new rate applied weekly. I need to join on the specific customer ID's but also need to match the orders with no specific customer entry in the CustSurcharge to the "******" entry. The first select below gives me exactly the records I need. The second one will give me two records for the customers with their own surcharge and I can ignore the duplicates when performing the update calculations. Is there a better way to do this? I looked into Top 1 with a Group By but decided it wasn't worth the effort because this is only going to be run once a week and the number of records involved is around 500 per week.

Select P.CustID, P.OrderNo, S.NewRate
 From Orders P
 Inner Join CustSurcharge S on P.CustID = S.CustID
 Where OrderDate > '20070701'

Union All 

Select P.CustID, P.OrderNo, X.NewRate
 from Orders P
 Inner Join CustSurcharge X on X.CustID = '******'
 Left Outer Join CustSurcharge S on P.CustID = S.CustID
 Where OrderDate > '20070701' And S.CustID Is Null

Order By P.CustID, P.OrderNo

Select P.CustID, P.OrderNo, S.NewRate, S.CustID From Orders P
 Inner Join CustSurcharge S on P.CustID = S.CustID or S.CustID = '******'
 Where OrderDate > '20070701'
 Order By P.CustID, P.OrderNo, S.CustID Desc

Auguy
Sylvania/Toledo Ohio

 

[NoCoolHandle]

what is wrong with a second union all? could something like this work.

Select P.CustID, P.OrderNo, S.NewRate, 0 as CustId2 From Orders P Inner Join CustSurcharge S on P.CustID = S.CustID Where OrderDate > '20070701'
Union All 
Select P.CustID, P.OrderNo, X.NewRate , 0 from Orders P Inner Join CustSurcharge X on X.CustID = '******' Left Outer Join CustSurcharge S on P.CustID = S.CustID Where OrderDate > '20070701' And S.CustID Is Null
union all
Select P.CustID, P.OrderNo, S.NewRate, S.CustID From Orders P Inner Join CustSurcharge S on P.CustID = S.CustID or S.CustID = '******' Where OrderDate > '20070701' Order By P.CustID, P.OrderNo, S.CustID Desc

sorry end of day..

 

[Auguy]

Thanks NoCool

Auguy
Sylvania/Toledo Ohio