>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.

Roy,
Maybe I can't see it myself but I don't see a need to join them. Isn't it simply:

select * from TBL_CUSTOMER_PRICES

? Also if you need a join instead of

ON a.CUST_ID+a.MFG+a.ITEM+a.CASE = b.a.Cust_id+a.MFG+a.ITEM+a.CASE

prefer:

ON a.CUST_ID = a.CUST_ID and a.MFG = b.MFG and a.ITEM = b.ITEM and a.CASE = b.case

style syntax. That syntax also makes your join conditions easier to implement (say if I misundersttod).
Cetin