Geek asks and answers

Sql server server request

I do not know what happened to this request:

select * from products , top 1 * from pic 
where products.productId = pic.productId

I have products and Pic tables, each product can have from 1 to n pic, and I would like to return each product and the first picture of this

Image chart can help enter image description here

+3
source share
3 answers

You should have a way to uniquely identify each figure, so I see this table as an identifier column ...

SELECT
  *
FROM
  products
LEFT JOIN
  pic
    ON pic.Id = (SELECT TOP 1 id FROM pic WHERE productID = products.ProductID ORDER BY id DESC)


EDIT

Inspired by another answer, using APPLY instead ...

SELECT
  *
FROM
  products
OUTER APPLY
  (SELECT TOP 1 * FROM pic WHERE productID = products.ProductID ORDER BY id DESC) AS pic
+3
source

You need a subquery for

  • select the first PicID for each ProductID
  • join the pic table itself to get extra columns
  • join products to get product columns

SQL statement

SELECT  *
FROM    Products prod
        LEFT OUTER JOIN Pic p ON p.ProductID = prod.ProductID
        LEFT OUTER JOIN (
          SELECT PicID = MIN(PicID)
                 , ProductID
          FROM   Pic
          GROUP BY
                 ProductID
        ) pm ON pm.PicID = p.PicID
0

, , , sub , TSQL

Select 
*
,(select top(1) adress from pic where pic.productid=products.id /* if u wanna you also can order by id */   ) as Id
from products
0

More articles:


All Articles