CODEWITHSUNDEEP
sqlsql-server
user441365
user441365

Reputation: 4032

T-SQL inner join first result of inner join

I have 2 tables, Property and PropertyImage. 1 Property has multiple images

I need to get all the properties and just 1 PropertyImage per Property.

Does anyone know how to accomplish that?

Property: PropertyID, Name
PropertyImage: PropertyImageID, PropertyID, Source, Type

Thanks

Upvotes: 3

Views: 3863

Answers (4)

Leons
Leons

Reputation: 2674

Here is an example where all the records from Property is selected and the last record from PropertyImage, assuming that the id column is an identity column and the last record is the one you need. You will need to adjust based on your exact column names and requirements.

SELECT p.PropertyID, p.Name, pi.PropertyImageID, pi.Source, pi.Type 
  FROM Property p 
       INNER JOIN PropertyImage pi
         ON pi.PropertyImageId = (SELECT MAX(sub.PropertyImageId) 
                                    FROM PropertyImage sub
                                   WHERE sub.PropertyId = p.PropertyId)

EDIT: Added column names in select

Upvotes: 6

ipr101
ipr101

Reputation: 24236

Try - 

Select Property.PropertyID
FROM Property
INNER JOIN PropertyImage pi on pi.PropertyID = Property.PropertyID 
WHERE pi.PropertyImageID = (SELECT MAX(PropertyImageID) FROM PropertyImage WHERE PropertyID = Property.PropertyID)

That should only get the PropertyImage with the highest 'PropertyImageID' value. You could change the MAX(PropertyImageID) logic depending on your needs.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453746

;WITH PI
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY PropertyID 
                                       ORDER BY (SELECT 0)) AS RN
         FROM   PropertyImage)
SELECT P.PropertyID,
       P.Name,
       PI.PropertyImageID,
       PI.Source,
       PI.Type
FROM   Property P
       JOIN PI
         ON P.PropertyID = PI.PropertyID
WHERE  PI.RN = 1

Upvotes: 2

xQbert
xQbert

Reputation: 35343

One way to do this would be to

SELECT P.PropertyID, P.Name, max(PI.PropertyImageID) 
from Property P 
INNER JOIN PropertyImage PI 
ON P.PropertyID = PI.PropertyID
GROUP BY P.PropertyID, P.name

Example data and desired output would help to provide a more complete answer.

Upvotes: 0

Related Questions