Solved

Master Details SQL Query

Posted on 2010-11-22
3
265 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 35
Dataset not reading table data 12 46
Why does this keep coming up NULL? 2 43
format nvarchar field as mm/dd/yyyy 4 61
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now