Posted by Paradyse on 07/06/06 16:54

MC,

I appreciate your help but it's giving me an error in the Group clause.
Any ideas?

Darin

"MC" <marko_culo#@#yahoo#.#com#> wrote in message
news:e8imdq$f5c$1@magcargo.vodatel.hr:

> 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]