CODEWITHSUNDEEP
sqlsql-serverselect
Dindar
Dindar

Reputation: 3235

sql server top query

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 enter image description here

Upvotes: 3

Views: 272

Answers (3)

MatBailie
MatBailie

Reputation: 86715

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

Upvotes: 3

adopilot
adopilot

Reputation: 4500

There is also way with subsection but please avoid sub select as much as You can in yours 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

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

You need a subquery to

  • select the first PicID's for each ProductID
  • join with Pic table itself to get the additional columns
  • join with Products to get the 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

Upvotes: 0

Related Questions