Left Join Top 1 not working - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Left Join Top 1 not working

Author

Topic

Oni
Starting Member

3 Posts

Posted - 2008-10-20 : 07:12:52

Hi Guys,

First off, sorry if this has been answered before. I have tried searching for this issue for the last few days on this forum and in Google but I don't seem to be getting anywhere fast!

So onto the issue! Currently I have a few tables that hold the property details for a property site I'm currently working on. The code below works perfectly - I can see all my properties and their images if I run the query.

The problem is that if someone uploads more than one image the results are duplicated. It doesn't just give me one property and the first image relating to that property.

SELECT P.PropertyID, P.PropertyValue, P.NoOfBedrooms,
C.CountyName, HS.HouseStyle, T.TownCityName, PI.ImageFile
FROM Property AS P
INNER JOIN HouseStyle HS ON P.HouseStyleIDFK = HS.HouseStyleID
INNER JOIN TownCity T ON P.TownCity = T.TownCityID
INNER JOIN County C ON C.CountyID = P.County
LEFT Join
(Select ImageFile,PropertyIDFK From PropertyImages)PI
ON P.PropertyID = PI.PropertyIDFK

I have tried the left join with top 1 in the PropertyImages select statement but that only gets the Top 1 from PropertyImages without a join - i.e. the first row in propertyImages.

I saw the post [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112856[/url] (Left join produces too many rows) and the descussion about using Common Table Expression but I could not get it working with my example for some reason and based on the comments it doesn't appear to be the best example of how to overcome this problem.

I know that I should be able to do this but I can't seem to get it working. Can anyone point me in the right direction here?

Thanks in advance for your help. If you need anymore info please let me know!

Rich

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:17:19

do you have a date column in PropertyImages? what field designates the order in which images are added for a property?

Oni
Starting Member

3 Posts

Posted - 2008-10-20 : 07:32:20

Hi Visakh16,

Thanks for the speedy reply. On the PropertyImages table the only fields are: PropertyImagesID, ProprtyIDFK and ImageFile. I don't timestamp any data entered in here as I'm not really worried about what image I show people on the front end. PropertyImagesID is the primary key for this table so that value will tell me the order of the images based on its value - higher being latest entered.

Rich

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2008-10-20 : 07:45:48

The point is that you want one picture of the property if there are any, for each property. To use TOP for that means you have to use an APPLY so that the join of the property ID is part of the subquery.


SELECT P.PropertyID, P.PropertyValue, P.NoOfBedrooms, 
C.CountyName, HS.HouseStyle, T.TownCityName, PI.ImageFile
FROM Property AS P 
INNER JOIN HouseStyle HS ON P.HouseStyleIDFK = HS.HouseStyleID 
INNER JOIN TownCity T ON P.TownCity = T.TownCityID 
INNER JOIN County C ON C.CountyID = P.County 
OUTER APPLY (
    Select TOP 1 ImageFile, PropertyIDFK
    From PropertyImages PI
    WHERE P.PropertyID = PI.PropertyIDFK
    ORDER BY PI.PropertyImagesID
  ) PI

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:47:19

quote:
Originally posted by Oni

Hi Visakh16,

Thanks for the speedy reply. On the PropertyImages table the only fields are: PropertyImagesID, ProprtyIDFK and ImageFile. I don't timestamp any data entered in here as I'm not really worried about what image I show people on the front end. PropertyImagesID is the primary key for this table so that value will tell me the order of the images based on its value - higher being latest entered.

Rich

does that mean you want a random image returned for each property?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-20 : 07:49:05

Or you use row_number() to select the higher PropertyImagesID :

SELECT P.PropertyID, P.PropertyValue, P.NoOfBedrooms,
C.CountyName, HS.HouseStyle, T.TownCityName, PI.ImageFile
FROM Property AS P
INNER JOIN HouseStyle HS ON P.HouseStyleIDFK = HS.HouseStyleID
INNER JOIN TownCity T ON P.TownCity = T.TownCityID
INNER JOIN County C ON C.CountyID = P.County
Join
(Select
ImageFile,
PropertyIDFK,
row_number() over (partition by PropertyIDFK order by PropertyImagesID DESC) as rownum
From PropertyImages)PI
ON P.PropertyID = PI.PropertyIDFK and PI.rownum=1

Webfred

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:54:52

[code]SELECT P.PropertyID, P.PropertyValue, P.NoOfBedrooms,
C.CountyName, HS.HouseStyle, T.TownCityName, PI.Image
FROM Property AS P
INNER JOIN HouseStyle HS ON P.HouseStyleIDFK = HS.HouseStyleID
INNER JOIN TownCity T ON P.TownCity = T.TownCityID
INNER JOIN County C ON C.CountyID = P.County
OUTER APPLY (
Select MAX(ImageFile) AS Image, PropertyIDFK
From PropertyImages PI
GROUP BY PropertyIDFK) PI
ON P.PropertyID = PI.PropertyIDFK[/code]

Oni
Starting Member

3 Posts

Posted - 2008-10-20 : 08:03:36

Hi Guys,

I tried the OUTER APPLY as suggested by Arnold and it works perfectly!

Thanks to Visa and webFred for your suggestions too! I'm amazed at the speed of all your replies on here. You have all really helped me out of a jam with this one so thanks again :D

Rich

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources