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

Question

question

user-1399 (google) asked Jun 20, '10

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

sql-server-2008 mysql

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 commented · Jun 21, 2010 at 12:23 AM

are you using MySQL or SQL Server 2008 ?

0 ·

Answer 1 · 2010-06-20T23:00:26Z

Scot Hauder answered Jun 20, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question