how to select one image of a object stored in another table

Question

product table in SQL tables:

Product: 
    ID,
    Name

ProductImage
    ID,
    ProductID,
    Image

I want to select an image in select query of Product I need first/last image of product1, first/last image of product2, etc

Something like:

select Product.id,Product.name,(select top(1)image from productimage where productimage.ProductID=product.ID)as Image from product

possible duplicate of [Get top 1 row of each group](http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — gbn, May 15 '13 at 13:13

Fuad · Accepted Answer · 2013-05-15T14:41:49.900

0

Try this,Maybe useful:

select Product.id,Product.name,
(select top (1) image from productimage where productimage.ProductID=product.ID order by productimage.ID asc)as FirstImage ,
(select top (1) image from productimage where productimage.ProductID=product.ID order by productimage.ID desc) as LastImage
from product

edited May 15 '13 at 14:41

answered May 15 '13 at 13:29

Fuad

231
2
10

score 0 · Answer 2 · answered Nov 06 '19 at 10:14

Temp Table subqueries are better for long run over column level subqueries.

Ex:

SELECT
    Product.id,
    Product.name,
    productImageTable.Image AS Image 
FROM Product INNER JOIN
(SELECT 
    ProductID,
    Min(image) as Image 
FROM productimage 
GROUP BY ProductID) AS productImageTable ON 
    productImageTable.ProductID=Product.ID

how to select one image of a object stored in another table

2 Answers2