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