Solved

Master Details SQL Query

Posted on 2010-11-22
3
269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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