CODEWITHSUNDEEP
sqlsql-server
crichavin
crichavin

Reputation: 4582

Obtain record in right table based on lowest value in middle cross reference table joined to left table

I have a Product table, a Category table, and a cross reference/mapping table in between. A product can belong to multiple categories. I want a query to produce the Product Id and the Category Name. As the product can belong to multiple categories, I want to only include the one that equates to the lowest value in Product_Category_Mapping.DisplayOrder.

enter image description here

Here is a limited sample data set from the three tables

Table: Product PC_Mapping Category
Column:(ID)    (DispOrd)  (Name)
Records
----------------------------------
       1       10         Milk   <----- just return this row
       1       20         Dairy
       1       30         Cheese

Upvotes: 0

Views: 26

Answers (2)

The Shooter
The Shooter

Reputation: 733

@John: I think your query needs little modification. I think Chad wants to pick with lowest value for each product so a group by should be added.

SELECT p.ID as [Product ID], c.Name as Category, m.x
FROM Product p INNER JOIN (
    SELECT ProductID, Min(DisplayOrder) as x FROM Product_Category_Mapping GROUP BY ProductId) m ON p.ID = m.ProductID 
    INNER JOIN Product_Category_Mapping pc ON p.ID = pc.ProductID AND m.x = pc.DisplayOrder
    INNER JOIN Category c ON pc.CategoryID = c.ID

Upvotes: 1

John Bingham
John Bingham

Reputation: 2006

I think this should do it, though simpler answers may be possible:

SELECT p.ID as [Product ID], c.Name as Category
FROM Product p INNER JOIN (
    SELECT ProductID, Min(DisplayOrder) as x FROM Product_Category_Mapping) m ON p.ID = m.ProductID 
    INNER JOIN Product_Category_Mapping pc ON p.ID = pc.ProductID AND m.x = pc.DisplayOrder
    INNER JOIN Category c ON pc.CategoryID = c.ID

Upvotes: 0

Related Questions