First image select? - 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 2000 Forums

Transact-SQL (2000)

First image select?

Author

Topic

mfazio
Starting Member

17 Posts

Posted - 2005-12-24 : 00:05:21

Hi guys, got myself into a pickle.. again

Basically, ive created a vehicle search... here is my current sql statement.

SELECT Vehicles.StockNumber, Models.Model, Makes.Make, Vehicles.Year, Vehicles.Colour, Vehicles.Gears, Vehicles.Transmission, Vehicles.EngineCapacity,
Vehicles.Turbo, Vehicles.FuelType, Vehicles.Gas, Vehicles.BodyType, Vehicles.Odometer, Vehicles.RetailPrice, Vehicles.Comments,
Images.ImageID
FROM Vehicles INNER JOIN
Models ON Vehicles.ModelID = Models.ModelID INNER JOIN
Makes ON Models.MakeID = Makes.MakeID LEFT OUTER JOIN
Images ON Vehicles.StockNumber = Images.StockNumber
WHERE (@MakeID = 0 OR
Models.MakeID = @MakeID) AND (@ModelID = 0 OR
Vehicles.ModelID = @ModelID) AND (@MinPrice = 0 OR
Vehicles.RetailPrice >= @MinPrice) AND (@MaxPrice = 0 OR
Vehicles.RetailPrice <= @MaxPrice) AND (@MinYear = 0 OR
Vehicles.Year >= @MinYear) AND (@MaxYear = 0 OR
Vehicles.Year <= @MaxYear)

You can really forget all the WHERE statements... but basically i want the vehicles to list with only the FIRST imageID from the image table associated to it...

In other words, vehicles table has an assosiated image table

MAKES 1---m MODELS 1---m VEHICLES 1---m IMAGES

I want all vehicles based on my sql statement and only the FIRST image from the image table corresponding to the vehicle, if there is an image present...

Thanks guys

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-24 : 09:42:55

Well you'll need to decide what determines the order of images but assuming it's ImageID then here are 3 alternatives as replacements for you join to Images:


LEFT OUTER JOIN Images ON Vehicles.StockNumber = Images.StockNumber
	and Images.ImageID = (select top 1 imageid 
				from	Images z
				where	z.StockNumber = Vehicles.StockNumber
				order by imageid asc)

LEFT OUTER JOIN Images ON Vehicles.StockNumber = Images.StockNumber
	and Images.ImageID = (select min(z.ImageID) 
				from	Images z
				where	z.StockNumber = Vehicles.StockNumber)


LEFT OUTER JOIN Images (select StockNumber, min(ImageID) ImageID
			from Images
			group by StockNumber) 
	as Images ON Vehicles.StockNumber = Images.StockNumber

You can try (modify as needed) these and compare the plans/execution times to see which works best for your actual data.

Be One with the Optimizer
TG

mfazio
Starting Member

17 Posts

Posted - 2005-12-24 : 10:19:10

Once again the people dont fail me.

Thanks mate, soloutions were spot on.

Have a merry christmas, if your into that sorta thing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources