Help with 2 table SQL Query

mgibson30 · June 23rd, 2003, 08:01 PM

I have a small problem with price lists in SQL.

I have one table with fields Item_num,item_cost,item_price

In another table I would like to apply special prices for specific Items in the main price list table. The structure of the 2nd table is

Account_num (Related to the customer account)
Item_num
disc_item_price

I would like to be able to execute an sql statement against both tables and return all items for a particular account number that are actively discounted and all other items from the standard price list (excluding the list prices of items that have discounts applied)

Can anyone help????????

Thanks

Jeff Mason · June 23rd, 2003, 08:38 PM

How about:

Code:

SELECT @Account_num, SP.Item_num, COALESCE(DP.disc_item_price,SP.item_price)
   FROM Std_prices SP LEFT JOIN Disc_prices DP
      ON SP.Item_num=DP.Item_num
     AND DP.Account_num=@Account_num;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

mgibson30 · June 23rd, 2003, 10:32 PM

Works perfectly! Thanks for your prompt response.

Quote:

quote:Originally posted by Jeff Mason
How about:

Code:

SELECT @Account_num, SP.Item_num, COALESCE(DP.disc_item_price,SP.item_price)
   FROM Std_prices SP LEFT JOIN Disc_prices DP
      ON SP.Item_num=DP.Item_num
     AND DP.Account_num=@Account_num;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com