Please Help with SQL Query Problem

[Darin L. Miller]

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

[MC]

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" <su*****@paradysed.comwrote in message
news:9N******************@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

[Paradyse]

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:e8**********@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" <su*****@paradysed.comwrote in message
news:9N******************@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

[Hugo Kornelis]

On Thu, 06 Jul 2006 16:54:17 GMT, Paradyse wrote:

>MC,

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

Hi Darin,

Please post the exact text of the error message (use copy and paste to
prevent typos). Also, post the complete query.

It would be better still if you also posted CREATE TABLE statements for
all tables involved, including constraints, properties and indexes, plus
a few INSERT statement with some sample data and expected results.

But we do need at least the error message and the query to help you!

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Paradyse (su*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Paradyse]

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" <es****@sommarskog.sewrote in message
news:Xn*********************@127.0.0.1:

Paradyse (su*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx