Need help with a SQL join: Conditional Matching?

Question

I'm joining 2 tables.

              ACCOUNTS
account_number    region    product
1234               100         AB
2345                 0         AB
3456               300         CD
4567                 0         CD

           PRODUCTS
product     region       price       
AB            100        $1.50
AB              0        $1.25
CD              0        $2.00

Each account record has a region and a product code Products are unique by product code and region.

However, the business logic says that if there is not a matching region in the PRODUCTS table, use the 0 region to get the price.

So I have to join by product and region to get the region specific pricing. In the example, accounts 1234, 2345 & 4567 find a matching record.

But I need to find a way for 3456 to join to the 0 region for product CD.

I suppose I could (ACCOUNTS left join PRODUCTS) and then update any null prices forcing region to 0 but how ugly is that?

Any ideas?

Also, I'm working with a vendor application so I can't change the schema or the data.

Database: MS SQLSERVER 2005

Answer 1

You should just be able to do a double join like this:

SELECT a.account_number, a.region, a.product, ISNULL(p1.price, p2.price) AS Price
FROM Accounts a
    LEFT JOIN Products p1 ON a.product = p1.product AND a.region = p1.region
    LEFT JOIN Products p2 ON a.product = p2.product AND p2.region = 0

Answer 2

Although not a 100% elegant solution this works in SQL Server. You should use a subquery to get the default price, but I did it this way for clarity.

DECLARE @Price DECIMAL(9,2)
SELECT @Price = price FROM Products WHERE Region = 0

SELECT
    A.accountNumber, 
    A.Product,
    ISNULL(P.Price, @Price) AS Price
FROM Accounts A
    LEFT OUTER JOIN Products P
        ON (A.Region = P.Region 
            AND A.Product = P.Product)

Answer 3

To me it's not ugly, it would be okay to do something like this

SELECT coalesce(regional.price, general.price) as EffectivePrice
FROM Accounts
LEFT JOIN Products as Regional 
     ON Account.Product = Regional.Product and Accounts.Region = Regional.Region
LEFT JOIN Products as General 
     ON Account.Product = General.Product and General.Region = 0