x

Problem with Master/Detail script displaying the Master Record with every Detail Record.

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


                This is what I need:                    

2000 Mack Truck 2 M355 Arborist Trucks - 12,000
Miles / Hours: 100,000
VIN / Serial No: 12345678
Description: TEST
Location: AREA
For more info contact: Dennis West - 541-830-0531
westdh@charter.net test@test.com
6/19/2010

_______ _________ _________

Picture 1 Picture 2 Picture 3

_______ _________ _________


1995 GM Truck 2 M355 Arborist Trucks - 12,000
Miles / Hours: 200,000
VIN / Serial No: 22222222
Description: TEST 3
Location: AREA 3
For more info contact: Dennis West - 541-830-0531
westdh@charter.net test@test.com
6/19/2010

_______ _________ _________

Picture 1 Picture 2 Picture 3

_______ _________ _________


2003 Ford Truck 2 M355 Arborist Trucks - 12,000
Miles / Hours: 300,000
VIN / Serial No: 4567890
Description: TEST 3
Location: AREA 3
For more info contact: Dennis West - 541-830-0531
westdh@charter.net test@test.com
6/19/2010

_______ _________ _________

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

more ▼

asked Jun 20, 2010 at 09:44 PM in Default

user-1399 (google) gravatar image

user-1399 (google)
1 1 1 2

are you using MySQL or SQL Server 2008 ?
Jun 21, 2010 at 12:23 AM Squirrel 1
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Jun 20, 2010 at 11:00 PM

Scot Hauder gravatar image

Scot Hauder
6.1k 13 15 18

Scot, shouldn't you have an ORDER BY clause inside the CROSS APPLY section ?
Jun 21, 2010 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, 2010 at 04:05 AM Scot Hauder
agreed. Maybe with the sample & result that OP has posted, someone can provide a solution :)
Jun 21, 2010 at 09:25 AM Squirrel 1
3*5 sub-selects to return all attributes for the 3 images in one row
Jun 21, 2010 at 11:38 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1853
x70

asked: Jun 20, 2010 at 09:44 PM

Seen: 960 times

Last Updated: Jun 21, 2010 at 01:43 AM