SQL Query Help - Group and Aggregate Functions
I have a request that I need help with, several tutorials have been checked but I haven't found this to be the problem.
I have three joined tables, Products, ProductImagesLookUp and Images.
A product can have any number of images and the order of images for the product, stored in ProductImagesLookUp.
I need to return a list of products with their main 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 request to cancel the list of individual products that have a low Order value. That is, this shoudl pulls back rows with ProductImagesLookUpId from 65 (product 7), 74 (product 14) and 79 (product 15).
Thanks in advance for your help. this man really made me pull my hair out!
a source to share
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 or generalize anything, as the subquery ensures that for any given ProductId
& mdash there is at most one result row; the one with the lowest Order
.
a source to share