I have a query I need some help with, have been checking out a fair few tutorials but nohting I found covers this issue.
我有一个查询,我需要一些帮助,已经检查了一些公平的教程,但没有我找到涵盖这个问题。
I have three joined tables, Products,ProductImagesLookUp and Images.
我有三个连接表,产品,ProductImagesLookUp和图像。
A product can have any number of Images and the Order of the Images for a Product are stored in ProductImagesLookUp.
产品可以包含任意数量的图像,产品图像的顺序存储在ProductImagesLookUp中。
I need to return a list of products with their primary Image (the one with the lowest order value).
我需要返回一个带有主映像的产品列表(具有最低订单值的产品)。
The list looks like this
列表看起来像这样
Product
Images
LookUpId FileID Order ProductTitle Price ProductId
65 2 1 Amari Summer Party Dress 29.99 7
66 1 2 Amari Summer Party Dress 29.99 7
67 3 3 Amari Summer Party Dress 29.99 7
74 4 5 Beach Cover Up 18.00 14
75 5 4 Beach Cover Up 18.00 14
76 7 6 Beach Cover Up 18.00 14
77 8 7 Beach Cover Up 18.00 14
78 9 8 Beach Cover Up 18.00 14
79 10 9 Amari Classic Party Dress 29.95 15
80 11 11 Amari Classic Party Dress 29.95 15
81 12 10 Amari Classic Party Dress 29.95 15
I want my query to pull back a list of distinct products which have the lowst Order value. I.e. it shoudl pull back the rows with the ProductImagesLookUpId of 65 (Product 7),74 ( Product 14) and 79 (Product 15).
我希望我的查询能够撤回具有最低Order值的不同产品列表。即它使用ProductImagesLookUpId为65(产品7),74(产品14)和79(产品15)来拉回行。
Thanks in advance for your help. this one has really had me pulling my hair out!
在此先感谢您的帮助。这个真的让我把头发拉出来!
1 个解决方案
#1
SELECT
l.LookupId,
i.FileId,
l.[Order],
p.ProductTitle,
p.Price,
p.ProductId
FROM
Products p
INNER JOIN ProductImagesLookUp l ON l.ProductId = p.ProductId
INNER JOIN Images i ON i.FileId = l.FileId
WHERE
i.[Order] = (
SELECT MIN([Order])
FROM ProductImagesLookUp
WHERE ProductId = p.ProductId
)
There is no need to group by or aggregate anything since the sub-query ensures that there is not more than a single result row for any given ProductId — the one with the lowest Order.
不需要分组或聚合任何内容,因为子查询确保任何给定的ProductId(具有最低Order的那个)的结果行不超过一个。
#1
SELECT
l.LookupId,
i.FileId,
l.[Order],
p.ProductTitle,
p.Price,
p.ProductId
FROM
Products p
INNER JOIN ProductImagesLookUp l ON l.ProductId = p.ProductId
INNER JOIN Images i ON i.FileId = l.FileId
WHERE
i.[Order] = (
SELECT MIN([Order])
FROM ProductImagesLookUp
WHERE ProductId = p.ProductId
)
There is no need to group by or aggregate anything since the sub-query ensures that there is not more than a single result row for any given ProductId — the one with the lowest Order.
不需要分组或聚合任何内容,因为子查询确保任何给定的ProductId(具有最低Order的那个)的结果行不超过一个。