sql server top query

Question

I don't know what's wrong with this query :

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

I have Products and Pic tables , every products could have 1 to n pic and I would like to return every product and the first pic of that

The picture of diagram may help

Answer 1

You need to have a way of uniquely identifying each pic, so I'm asuming that table as an ID 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