Hi,

I have 3 tables:
- table of customers TBL_CUSTOMER.
- table of prices TBL_CUSTOMER_PRICES
- table of items TBL_ITEMS.

The table of TBL_CUSTOMER_PRICES has 4 fields:
1) Customer # (CUST_ID)
2) Manufacturer (MFG)
3) Item (ITEM)
4) Case (CASE)
5) Price

Now the table of TBL_ITEMS has 4 fields:
1) Customer # (CUST_ID)
2) Manufacturer (MFG)
3) Item (ITEM)
4) Case (CASE)

Now I want to get a list of all the items from TBL_ITEMS with the corresponding PRICE from TBL_CUSTOMER_PRICES.

This seems simple enough, the key to get the PRICE would be the (CUST_ID+MFG+ITEM+CASE) and that would give the PRICE for the item(s) for the chosen customer.

The query would look something like this:

SELECT a.CUST_ID,a.MFG,a.ITEM,a.CASE,b.Price
FROM TBL_ITEMS a
LEFT JOIN TBL_CUSTOMER_PRICES b
ON a.CUST_ID+a.MFG+a.ITEM+a.CASE = b.a.Cust_id+a.MFG+a.ITEM+a.CASE

The problem is that not all the customers use the MFG+ITEM+CASE Key, some use MFG+ITEM and some use MFG+CASE to get a match to retrieve it's prices. For example:

For customers setup to look for MFG+ITEM
SELECT a.Cust_id,a.MFG,a.ITEM,a.CASE,b.Price
FROM ITEM_TABLE a
LEFT JOIN TBL_CUSTOMER_PRICES b
ON a.Cust_id+a.MFG+a.ITEM = b.a.Cust_id+a.MFG+a.ITEM


For customers setup to look for MFG+CASE
SELECT a.Cust_id,a.MFG,a.ITEM,a.CASE,b.Price
FROM ITEM_TABLE a
LEFT JOIN TBL_CUSTOMER_PRICES b
ON a.Cust_id+a.MFG+a.CASE = b.a.Cust_id+a.MFG+a.CASE

I want to get the Prices for each customer based on how they were set up, is this possible in one SQL Statement? Basically somehow combine all 3 Queries here into one, of course adding a join to the CUSTOMER table to retrieve which way it should match (MFG+ITEM+CASE or MFG+ITEM or MFG+CASE).

Thanks.