|
|
Posted by Paradyse on 07/07/06 00:48
Thanks for your comments.
I am a noob when it comes to SQL related issues. Some of the things you
requested are way over my head. I apologize for my ignorance.
I did however fix the issue with you and MC's recommendation plus a
lucky guess on my own. The final query I use is as follows:
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, Photos.Path
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
(SELECT MIN(dbo.ShowcasePhotos.PhotoPath)
AS Path, RecordID
FROM dbo.ShowcasePhotos
GROUP BY RecordID) Photos ON
dbo.ShowcaseRides.RecordID = Photos.RecordID INNER JOIN
dbo.ShowcasePhotos ON Photos.Path =
dbo.ShowcasePhotos.PhotoPath
WHERE (dbo.ShowcaseRides.MemberLive = 1) AND (dbo.ShowcaseRides.Live
= 1)
ORDER BY dbo.ShowcaseRides.EntryDate DESC
I greatly appreciate all of your help.
Regards,
Darin L. Miller
Paradyse Development
~-~-~-~-~-~-~-~-~-~-~-~-~-~-
"Some things are true whether you believe them or not." - Nicolas Cage
in City of Angels
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97F967778183Yazorman@127.0.0.1:
> Paradyse (support@paradysed.com) writes:
> > I appreciate your help but it's giving me an error in the Group clause.
> > Any ideas?
>
> There is a general recommendation for questions about help with queries,
> and that is that you post:
>
> o CREATE TABLE statements for your table(s). (Preferably simplified for
> the problem at hand).
> o INSERT statements with sample data.
> o The desired output given the sample.
>
> That makes it very easy to copy and paste into a query tool to develop
> a tested solution. It also shows that you are prepared make some effort
> yourself to solve the problem.
>
> Since you did not post any sample data, MC posted an untested solution.
> Apparently it had an error, but at least you got something to work from.
> The error I can spot in his suggestion is that dbo.Photos.RecordID should
> be Photos.RecordID. Photos here is a derived table, a virtual table
> constructed on the fly in the middle of the query; a very powerful
> construct.
>
> So try to work from this. If you still get an error message, and get passed
> it - make yourself a favour and post the error message next time.
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|