problems writing a Stored Procedure

[INFORMAT]

Hi,
I'm having a lot of troubles writing a stored procedure.

I have buyers and those buyers buy a product. A product has a standard price, but the user can sell to a buyer at a price wich is not equal to the original price.

In my query I want to have 1 record for each article, with the price. if the price for each buyer is the same, I want to see the standard price,otherwise I want to see in my record a null value for the price.

could anyone help me with this difficult issue?

Björn X-)

 

[tlbroadbent]

I'm not sure if I understand your requirement. It seems that you want to query your database and return a list of products or articles. If all of the article purchases were made at the standard price then show that price in the result set. Otherwise, the price should show NULL. If this is not correct please provide more information.

You provided no information about table structures so I can't help much except to give a general example. This example assumes that purchases are recorded in the PurchaseTbl and Products are listed in the ProductTbl. I use the CASE statement and a correlated subquery to determine variance from standard price.
[tt]
Select
ProductID,
Price=
CASE
WHEN 0=(SELECT COUNT(*) FROM PurchaseTbl
WHERE ProductID=p1.ProductID
AND PurPrice<>p1.Price)
THEN Price
ELSE NULL
END
FROM ProductTbl AS p1[/tt] Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous