Posted by MC on 07/06/06 09:44

You need to decide which photo you want to show if there are more then one.
You can add atribute (something like defaultPhoto) or something like that
and then filter by it. Otherwise, you can specify subquery and in it return
one photo for each recordID. Instead of:
INNER JOIN
dbo.ShowcasePhotos ON dbo.ShowcaseRides.RecordID =
dbo.ShowcasePhotos.RecordID

you can write

INNER JOIN
(select min(PhotoPath) as Path, RecordID from dbo.ShowcasePhotos group
by recordID) Photos ON dbo.ShowcaseRides.RecordID = dbo.Photos.RecordID

This finds the min(PhotoPath). You can use max() ot whatever that gets one
photo per recordID


MC


"Darin L. Miller" <support@paradysed.com> wrote in message
news:9N3rg.11460$vl5.8734@tornado.ohiordc.rr.com...
> I'm not too good with advanced SQL queries, so please bear with me on
> this.
>
> I have a query with multiple joins that I am trying to get just the last
> 10 of each unique record (RecordID) displayed. The problem is that some
> records have more than one photo assigned to the same RecordID and I only
> need one of them displayed for the results table. All photos will be
> displayed on the details page. I tried to use the DISTINCT command but it
> returns more than one of the same RecordID since the PhotoPath is
> different for each image. How does this query need to be written to solve
> this problem and display the records correctly? I am completely lost on
> this issue after hours of trying everything and anyone's prompt help will
> be greatly appreciated.
>
> SELECT DISTINCT
> TOP 10 dbo.ShowcaseRides.RecordID, dbo.ShowcaseRides.CustomerID,
> dbo.ShowcaseRides.PhotoLibID, dbo.ShowcaseRides.Year,
> dbo.ShowcaseRides.MakeShowcase, dbo.ShowcaseRides.ModelShowcase,
> dbo.ShowcaseRides.VehicleTitle, dbo.ShowcaseRides.NickName,
> dbo.ShowcaseRides.SiteURL, dbo.ShowcaseRides.ShowcaseRating,
> dbo.ShowcaseRides.ShowcaseRatingImage, dbo.ShowcaseRides.ReviewDate,
> dbo.ShowcaseRides.Home, dbo.ShowcaseRides.EntryDate,
> dbo.Customers.UserName, dbo.Customers.ShipCity, dbo.Customers.ShipRegion,
> dbo.Customers.ShipPostalCode, dbo.Customers.ShipCountry,
> dbo.Customers.LastName, dbo.Customers.FirstName,
> dbo.Customers.MemberSince, dbo.ShowcaseRides.Live,
> dbo.ShowcaseRides.MemberLive, dbo.Accessories.Make, dbo.Accessories.Model,
> dbo.ShowcasePhotos.PhotoPath
> FROM dbo.ShowcaseRides INNER JOIN
> dbo.Customers ON dbo.ShowcaseRides.CustomerID =
> dbo.Customers.CustomerID INNER JOIN
> dbo.Accessories ON dbo.ShowcaseRides.MakeShowcase =
> dbo.Accessories.MakeShowcase AND
> dbo.ShowcaseRides.ModelShowcase = dbo.Accessories.ModelShowcase
> INNER JOIN
> dbo.ShowcasePhotos ON dbo.ShowcaseRides.RecordID =
> dbo.ShowcasePhotos.RecordID
> WHERE (dbo.ShowcaseRides.MemberLive = 1) AND (dbo.ShowcaseRides.Live = 1)
> ORDER BY dbo.ShowcaseRides.EntryDate DESC
>
> Regards,
>
> Regards,
>
> Darin L. Miller
> Paradyse Development
> ~-~-~-~-~-~-~-~-~-~-~-~-~-~-
> "Some things are true whether you believe them or not." - Nicolas Cage in
> City of Angels
>

[Back to original message]