Chad Richardson Chad Richardson - 11 months ago 43
SQL Question

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)
1 10 Milk <----- just return this row
1 20 Dairy
1 30 Cheese


@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
    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