?
Solved

Master Details SQL Query

Posted on 2010-11-22
3
Medium Priority
?
273 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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

743 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