|
|
Posted by Darin L. Miller on 07/06/06 08:24
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
Navigation:
[Reply to this message]
|