|
Question: I have included the sql script I am having a problem with. How can it be resolved. I want (1) combined (equipment and UserProfile table) to be displayed, with (3) Picture Image's that are Joined to the CustomerImages Table. The below code doesn't do it .. It displays the combined (equipment and UserProfile table)with each Picture Image's: Please HELP What I need is a 1 to 3 ratio. -------------------------------------------------------------------------------------------SELECT Equipment.Year, Equipment.Make, Equipment.Model, Equipment.Type, Equipment.MilesHours, Equipment.VinSerial, Equipment.Description, Equipment.Location, Equipment.Price, Equipment.SignUpDate, UserProfile.Name, UserProfile.Company, UserProfile.Phone, UserProfile.WebsiteURL, UserProfile.Email, Equipment.EquipmentID, CustomerImages.ImageID, CustomerImages.EquipmentID AS Expr1, CustomerImages.Title, CustomerImages.ImageData, CustomerImages.MIMEType FROM Equipment INNER JOIN UserProfile ON Equipment.UserId = UserProfile.UserId LEFT OUTER JOIN CustomerImages ON CustomerImages.EquipmentID = Equipment.EquipmentID GROUP BY Equipment.Year, Equipment.Make, Equipment.Model, Equipment.Type, Equipment.MilesHours, Equipment.VinSerial, Equipment.Description, Equipment.Location, Equipment.Price, Equipment.SignUpDate, UserProfile.Name, UserProfile.Company, UserProfile.Phone, UserProfile.WebsiteURL, UserProfile.Email, Equipment.EquipmentID, CustomerImages.ImageID, CustomerImages.EquipmentID, CustomerImages.Title, CustomerImages.ImageData, CustomerImages.MIMEType ORDER BY Equipment.SignUpDate DESC 2000 Mack Truck 2 M355 Arborist Trucks - 12,000 _______ _________ _________ Picture 1 Picture 2 Picture 3 _______ _________ _________ 1995 GM Truck 2 M355 Arborist Trucks - 12,000 _______ _________ _________ Picture 1 Picture 2 Picture 3 _______ _________ _________ 2003 Ford Truck 2 M355 Arborist Trucks - 12,000 _______ _________ _________ Picture 1 Picture 2 Picture 3 _______ _________ _________ This is what I am returning & also what your script is returning : which is ok except that I am printing duplicate info for all the fields that are the same. if thier was a way to have duplicate field values Null. that would work I believe.2001 Ford M250 Utility Trucks 100,000 12345678 Good Garage 12,000 6/19/2010 5:23:05 PM 5a8af714-0373-4061-9f4e-4bc7b770d2a3 test@test.com westdh@charter.net 2367520f-bb14-4360-bb50-bea74586527b 5a8af714-0373-4061-9f4e-4bc7b770d2a3 Ford 1 image/jpeg 2001 Ford M250 Utility Trucks 100,000 12345678 Good Garage 12,000 6/19/2010 5:23:05 PM 5a8af714-0373-4061-9f4e-4bc7b770d2a3 test@test.com westdh@charter.net 2367520f-bb14-4360-bb50-bea74586527b 5a8af714-0373-4061-9f4e-4bc7b770d2a3 ford 2 image/jpeg 2001 Ford M250 Utility Trucks 100,000 12345678 Good Garage 12,000 6/19/2010 5:23:05 PM 5a8af714-0373-4061-9f4e-4bc7b770d2a3 test@test.com westdh@charter.net 2367520f-bb14-4360-bb50-bea74586527b 5a8af714-0373-4061-9f4e-4bc7b770d2a3 Ford 3 image/jpeg
(comments are locked)
|
|
If you post a couple lines of output showing what you expect we can give a better answer. I'm not clear if this is what you are looking for.
SELECT e.[Year]
, e.Make
, e.Model
, e.[Type]
, e.MilesHours
, e.VinSerial
, e.[Description]
, e.Location
, e.Price
, e.SignUpDate
, e.Name
, e.Company
, e.Phone
, e.EquipmentID
, up.WebsiteURL
, up.Email
, x.ImageID
, x.Expr1
, x.Title
, x.ImageData
, x.MIMEType
FROM Equipment e
JOIN UserProfile up ON (e.UserId = up.UserId)
CROSS APPLY (SELECT TOP 3 ci.ImageID
, ci.EquipmentID [Expr1]
, ci.Title
, ci.ImageData
, ci.MIMEType
FROM CustomerImages ci
WHERE ci.EquipmentID = e.EquipmentID)x
ORDER BY e.SignUpDate DESC
Scot, shouldn't you have an ORDER BY clause inside the CROSS APPLY section ?
Jun 21 '10 at 12:24 AM
Squirrel 1
Yes, I could have guessed the 3 most recent pictures but I am not even clear if the result set is what they are expecting. Now, I think they want one row for everything so 3 sub-selects to return all image info along with the other data all in one row
Jun 21 '10 at 04:05 AM
Scot Hauder
agreed. Maybe with the sample & result that OP has posted, someone can provide a solution :)
Jun 21 '10 at 09:25 AM
Squirrel 1
3*5 sub-selects to return all attributes for the 3 images in one row
Jun 21 '10 at 11:38 AM
Scot Hauder
(comments are locked)
|
are you using MySQL or SQL Server 2008 ?