You can achive your goal 2 ways. The code below assumes that you have column How2Join in the TBL_CUSTOMER table with the list of fields to use in JOIN. You'll have to adjust it for your data.
SELECT i.CUST_ID, i.MFG, i.ITEM, i.[CASE], cp.Price
FROM TBL_ITEMS i
JOIN TBL_CUSTOMER c ON c.cust_id = i.cust_id
LEFT JOIN TBL_CUSTOMER_PRICES cp
ON i.CUST_ID = cp.CUST_ID
AND i.MFG = cp.mfg
AND i.ITEM = cp.item
AND i.[CASE] = cp.[case]
WHERE c.How2Join = 'MFG+ITEM+CASE'
UNION ALL
SELECT i.CUST_ID, i.MFG, i.ITEM, i.[CASE], cp.Price
FROM TBL_ITEMS i
JOIN TBL_CUSTOMER c ON c.cust_id = i.cust_id
LEFT JOIN TBL_CUSTOMER_PRICES cp
ON i.CUST_ID = cp.CUST_ID
AND i.MFG = cp.mfg
AND i.[CASE] = cp.[case]
WHERE c.How2Join = 'MFG+CASE'
UNION ALL
SELECT i.CUST_ID, i.MFG, i.ITEM, i.[CASE], cp.Price
FROM TBL_ITEMS i
JOIN TBL_CUSTOMER c ON c.cust_id = i.cust_id
LEFT JOIN TBL_CUSTOMER_PRICES cp
ON i.CUST_ID = cp.CUST_ID
AND i.MFG = cp.mfg
AND i.ITEM = cp.item
WHERE c.How2Join = 'MFG+ITEM'
SELECT i.CUST_ID, i.MFG, i.ITEM, i.[CASE], cp.Price
FROM TBL_ITEMS i
JOIN TBL_CUSTOMER c ON c.cust_id = i.cust_id
LEFT JOIN TBL_CUSTOMER_PRICES cp
ON i.CUST_ID = cp.CUST_ID
AND (i.MFG = cp.mfg OR NOT c.How2Join LIKE '%MFG%')
AND (i.ITEM = cp.item OR NOT c.How2Join LIKE '%ITEM%')
AND (i.[CASE] = cp.[case] OR NOT c.How2Join LIKE '%CASE%')
>
>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.
--sb--
