Master Details SQL Query

I have two tables , the first table list all the products that are available for sale.
PRODUCT_REGISTRATION
======================
PRODUCT_ID int,
PRODUCT_DESCRIPTION
QUANTITY
....etc

The next table is the Vendor Pricing table where the table holds the price offers from various vendors for a given product.

VENDOR_PRICING
================
ID int
VENDOR_ID int
PRODUCT_ID int
PRICE decimal (18,2)

Now i need to list a view with all the product with the cheapest price.

ie

PRODUCT_REGISTRATION

1,'Samsung LCD',21
2, 'Samsung Camera',250

VENDOR_PRICING

1,1,1,2000
2,1,2,160
3,2, 1,2200
4,3,1,1900

etc

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
AS
SELECT
  VP.ID,
 P.PRODUCT_ID,
 P.PRODUCT_DESCRIPTION,
 P.QUANTITY,
 VP.VENDOR_ID ,
 SELECT MAX(PRICE) FROM VENDOR_PRICING WHERE VP.PRODUCT_ID=P. PRODUCT_ID AS PRICE



WHERE
   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
TECH_NETAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you want to use MIN(price), not MAX(Price)  ,...


so, let's go this way
CREATE VIEW dbo.PRODUCT_PRICING_VENDOR_VIEW
AS
SELECT *
  FROM ( SELECT VP.ID,
 P.PRODUCT_ID,
 P.PRODUCT_DESCRIPTION,
 P.QUANTITY,
 VP.VENDOR_ID ,
 VP.PRICE,
 ROW_NUMBER() OVER (PARTITION BY P.PRODUCT_ID ORDER BY VP.PRICE ASC ) rn
FROM VENDOR_PRICING VP 
JOIN PRODUCT_REGISTRATION P 
  ON VP.PRODUCT_ID=P. PRODUCT_ID
) sq
WHERE sq.RN = 1

Open in new window

0
 
Priya PerumpilavilSoftware EngineerCommented:
try this

select PRODUCT_ID ,
PRODUCT_DESCRIPTION,MIN(PRICE) from VENDOR_PRICING v,PRODUCT_REGISTRATION p  where v.PRODUCT_ID=p.PRODUCT_ID
0
 
TECH_NETAuthor Commented:
As always angelIII comes to my rescue. Perfect resolution.
0
All Courses

From novice to tech pro — start learning today.