One to Many Relationship Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

One to Many Relationship Question

Author

Topic

spsubaseball
Starting Member

17 Posts

Posted - 2012-01-27 : 17:01:19

I have the following SQL statement:

SELECT m.[property_id], m.[Address], m.[City], m.[Zip], m.County, m.Bedrooms, m.Bathrooms, m.HalfBathrooms, m.[V_O], m.[Stars], m.Rehab, m.Open_bid, m.Notes, m.date_time_changed, i.imagepaththumb FROM [Master] m LEFT JOIN Image i On property_id = property_id_fk where Active_Month='Y' ORDER BY m.[V_O] DESC, m.date_time_changed desc, m.[Address] ASC

The problem I'm having writing this sql statement is when I'm populating my gridview in asp.net if an imagepaththumb has more than one picture for that property I'm getting duplicate rows because there's different pictures. How would I use this following sql statement and just get the "top" entry for that property so I can put a logo showing that property has a picture? Can someone please help me with this!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-27 : 17:45:28

[code]
SELECT m.[property_id], m.[Address], m.[City], m.[Zip], m.County, m.Bedrooms, m.Bathrooms, m.HalfBathrooms, m.[V_O], m.[Stars], m.Rehab, m.Open_bid, m.Notes, m.date_time_changed, i.imagepaththumb
FROM [Master] m
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY property_id ORDER BY id) AS Rn,* FROM Image) i
On property_id = property_id_fk
AND Rn=1
where Active_Month='Y'
ORDER BY m.[V_O] DESC, m.date_time_changed desc, m.[Address] ASC
[/code]

id is your unique valued (pk) column of image table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spsubaseball
Starting Member

17 Posts

Posted - 2012-01-28 : 01:01:14

visakh16, thank you so much. This is exactly what I was looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-28 : 17:45:59

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources