Pavan V Parekh Pavan V Parekh - 1 month ago 5
SQL Question

I am trying to combine two tables (Master and child) into one. I need only one record of child table in stored procedure

I am trying to write a stored procedure for one record of a child table with in a Master table

I have Two Tables.


  • Master "Property"

  • Child "PropertyImage"



I need all the details of
Property
with only one image.

In child table, one property may have many images.

Property (propertyId, title)

propertyImage (imageId, FK_propertyId, imageFile)


E.g.

property (1, smallbuilding)
property (2, largebuilding)

propertyImage(1, 1, 1.jpg)
propertyImage(2, 1, 2.jpg)
propertyImage(3, 1, 3.jpg)
propertyImage(4, 1, 4.jpg)
propertyImage(5, 2, 7.jpg)
propertyImage(6, 2, 8.jpg)


My requirement is:

MyProperty(propertyId, title, imageFile)
Myproperty(1, smallbuilding, 1.jpg)
Myproperty(2, bigbuilding, 2.jpg)

Answer

Sound like you need this

;WITH cte AS
 (
  SELECT ROW_NUMBER() OVER (PARTITION BY propertyId ORDER BY imageId) AS rn,
         propertyId, title, imageFile
  FROM Property p JOIN propertyImage pr ON p.propertyId = pr.FK_propertyId
  )
  SELECT propertyId, title, imageFile
  FROM cte
  WHERE rn = 1