Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Master Details SQL Query

Posted on 2010-11-22
3
Medium Priority
?
274 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:TECH_NET
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34188105
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
 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 34188135
try this

select PRODUCT_ID ,
PRODUCT_DESCRIPTION,MIN(PRICE) from VENDOR_PRICING v,PRODUCT_REGISTRATION p  where v.PRODUCT_ID=p.PRODUCT_ID
0
 

Author Closing Comment

by:TECH_NET
ID: 34188598
As always angelIII comes to my rescue. Perfect resolution.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Integration Management Part 2

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question