CODEWITHSUNDEEP
sqlsql-server-2008select
wdharrison
wdharrison

Reputation: 13

SQL Comparing 2 tables while limiting data

The table and column names are vague because I am in the healthcare industry and unable to share specific details. I am using this Query to show the amount of savings to a customer if they purchase a product from my company(Table 1) instead of their current vendor (table2).

I have 2 tables like this on an MSQL Server 2008:

Table 1

ProductID, Description, Vendor, Price

Table 2

ProductID, Description, Price

I want to select every row from Table 2 and the matching data from Table 1. But I only want to return the vendor with the best price (the lowest price among vendors) from Table 1, not every vendor. So for any ProductID in Table 2 there should be one match from Table1, or a NULL value if there is no matching ProductID in Table 1. I joined the tables on ProductID and returned all the columns I wanted, but I cannot get it to limit to only one result from Table 1. If I do this with 1000 rows in Table 2 I should return 1000 rows. I keep ended with a few extra from the multiple vendor matches.

The results should look like this:

T1.ProductID, T1.Description, Vendor, T1.Price, T2.ProductID,
T2.Description, T2.Price, (T2.Price - T1.Price) as 'Amount Saved'

The SQL I have written is fairly simple:

SELECT 
    T1.ProductID, 
    T1.Description, 
    Vendor, 
    T1.Price, 
    T2.ProductID, 
    T2.Description,
    T2.Price, 
    (T2.Price - T1.Price) AS 'Amount Saved'

FROM 
    Table2 T2 LEFT OUTER JOIN Table1 T1 
        ON T2.ProductID = T1.ProductID
ORDER BY T2.ProductID

This answer from D. Stanley worked; with a minor change to select each row with the lowest price.

SELECT 
    T1.ProductID,
    T1.Description,
    T1.Vendor,
    T1.Price,
    T2.ProductID,
    T2.Description, 
    T2.Price, 
   (T1.Price - T2.Price) as 'Amount Saved'
FROM Table2 T2
LEFT JOIN (
    SELECT * FROM (
       SELECT ProductID, Description, Vendor, Price,
       ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Price ASC) AS Row
       FROM Table1) as result
    WHERE row=1
    )  AS T1
    ON T2.ProductID = T1.ProductID

Upvotes: 1

Views: 176

Answers (3)

konkked
konkked

Reputation: 3231

Even thought there are some better preforming query possibilities I like this way, if you want to include all possibilities with min cost just delete the outer query.

If I am incorrect about the table names please let me know this is what I assumed you were asking for because I am not 100% sure what you mean by "best price" but I assume it was lowest based off of your 'Amount Saved' field that was selected.

SELECT
    DISTINCT(T1.ProductId) AS ProductId,
    Description,
    Vendor,
    Price,
    SupplyDescription,
    SupplyPrice,
    AmountSaved AS 'Amount Saved' 
FROM
(
    SELECT 
        T1.ProductId AS ProductId, 
        T1.Description,
        T2.Vendor,
        T1.Price, 
    /*  T2.ProductId you are joining on this no need to include again*/
        T2.Description AS SupplyDescription,
        T2.Price AS SupplyPrice ,
        (T1.Price - ISNULL(T2.Price,T1.Price)) as AmountSaved
    FROM
    Product T1 WITH(NOLOCK)
    LEFT OUTER JOIN Supply T2 WITH(NOLOCK)
        ON T1.ProductId = T2.ProductId
    HAVING T2.Price = MIN(T2.Price)
) tt

NOTE: This is assuming the tables Product and Supply are not updated dynamically throughout the day, or change very infrequently, if this isn't the case (Product and Supply tables are updated frequently) and you could not justify the dirty reads produced by avoiding locking just remove the WITH(NOLOCK) hints from the query

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

It's not clear, what you mean with "best price". Just adjust the MAX() function to MIN() function if it's not what you want.

SELECT T1.ProductID, T1.Description, Vendor, T1.Price, T2.ProductID,
T2.Description, T2.Price, (T1.Price - T2.Price) as 'Amount Saved'
FROM (
SELECT
ProductID, Vendor, Description, Price
FROM Table1 t
WHERE Price = (SELECT MAX(Price) FROM Table1 subT WHERE t.ProductID = subT.ProductID)
) T1 RIGHT JOIN 
Table2 T2 ON T1.ProductID = T2.ProductID
  • Here's something you should read.

EDIT: I read now, that it's for SQL Server. The solution I provided and the additional reading material work for SQL Server, too. Although it's from MySQL manual, but there's just standard SQL involved.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152501

You can use ROW_NUMBER to find the "best" matching row from Table1:

SELECT 
    T1.ProductID, 
    T1.Description, 
    T1.Vendor, 
    T1.Price, 
    T2.ProductID,
    T2.Description, 
    T2.Price, 
    (T1.Price - T2.Price) as 'Amount Saved'
    FROM Table2 T2
    LEFT JOIN (
        SELECT ProductID, Description, Vendor, Price,
           ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Price DESC) Row
           FROM Table1
        ) T1
        ON T2.ProductID = T1.ProductID

Upvotes: 1

Related Questions