TECH_NET
asked on
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As always angelIII comes to my rescue. Perfect resolution.
select PRODUCT_ID ,
PRODUCT_DESCRIPTION,MIN(PR