I have two tables , the first table list all the products that are available for sale.
The next table is the Vendor Pricing table where the table holds the price offers from various vendors for a given product.
PRICE decimal (18,2)
Now i need to list a view with all the product with the cheapest price.
2, 'Samsung Camera',250
In the above illustation, the Vendor with ID=3 has the cheapest price for the product id=1.
So i wish to create a view to display the same.
CREATE VIEW dbo.PRODUCT_PRICING_VENDOR_VIEW
SELECT MAX(PRICE) FROM VENDOR_PRICING WHERE VP.PRODUCT_ID=P. PRODUCT_ID AS PRICE
VENDOR_PRICING VP LEFT OUTERJOIN
PRODUCT_REGISTRATION P ON VP.PRODUCT_ID=P. PRODUCT_ID
Can you let me know if this is the method to find the cheapest price.
If not can you change the query to help me get the result i wanted