How to return the value to a view from multiple selects in a if statement?

Question

I'm new to SQL and I'm trying to figure out, how to create a view in which the price of a product changes accordingly - if it has a discount or not. How would I go about this? I think I'm reaching in the wrong direction. Probably something to do with JOINS? Any suggestions are welcome!

CREATE VIEW AvailableProductsAndTheirPrices AS
SELECT Product.Name,

Product.Price,

InStock.Quantity, Store.Name, Store.Adress
FROM Product, InStock, Store
WHERE
InStock.Quantity > 0 AND
Product.PID = InStock.PID AND -- PID as in Product ID 
InStock.FID = Store.FID -- FID as in Store ID

GO

The Product.Price line would be replaced by something like this:

(SELECT IIF( (SELECT Product.PID NOT IN (SELECT Product.PID FROM Discount)),
             (SELECT Product.Price FROM .. how does this work?[1]), -- if there is no discount
             (SELECT Product.Price * Discount.Discount -- otherwise apply discount to price
              FROM Product, Discount
              WHERE Product.PID = Discount.PID AND GETDATE() <= Discount.To)
           ) 
),

[1] If I'm not mistaken, it would return the first row's value.

Answer 1

First, I'd recommend using the standard JOIN syntax. Then you can use an OUTER JOIN to the discount table.

CREATE VIEW AvailableProductsAndTheirPrices AS
SELECT P.Name,
       P.Price * COALESCE(D.Discount,1),
       I.Quantity, 
       S.Name, 
       S.Adress
FROM Product P
       INNER JOIN InStock I ON P.PID = I.PID
       INNER JOIN Store S ON P.FID = I.FID
       LEFT JOIN Discount D ON P.PID = D.PID AND D.To > Now() 
WHERE I.Quantity > 0  
GOm