----- Original Message ----- From: "Rhino" <[EMAIL PROTECTED]> To: "mysql" <mysql@lists.mysql.com> Sent: Thursday, December 30, 2004 9:03 AM Subject: Need help with SELECT
> I am trying to think of SQL that will let me show a one to many relationship > as a single row in a result set. They say a picture is worth a thousand > words so let me draw that picture. > > Event_ID Event_Name etc. > 1 Concert > 2 Art Exhibit > 3 Spelling Bee > [Primary key: Event_ID] > > > Event_ID File_Number File > 1 1 promoter_logo.jpg > 1 2 venue_logo.jpg > 1 3 performer_graphic.jpg > 2 1 artist_graphic.jpg > [Primary Key: Event_ID, File_Number] > > > Desired Result: > Event_ID Event_Name File File > File > 1 Concert promoter_logo.jpg venue_logo.jpg > performer_graphic.jpg > 2 Art Exhibit artist_graphic.jpg > 3 Spelling Bee > > > There is one record in the Events table for each event that an organization > is promoting. For each event, there could be 0 to 'n' files that give more > information about the event, such as pictures, audio clips, or whatever. I > want to display all the information about the event, including all of the > file names for that event, on a single result set row for each event, > regardless of how many files exist for that event. [At the moment, there is > a limit of 3 files per event but that could increase.] Here are two ways, but both are kind of hoaky. They both assume you know the number of files you want to display. Method 1 - with subqueries SELECT DISTINCT EventName, (SELECT f1.FileName from EventFiles as f1 where f1.EventID = e.EventID and f1.FileID = 1), (SELECT f2.FileName as File2 from EventFiles as f2 where f2.EventID = e.EventID and f2.FileID =2), (SELECT f3.FileName as File3 from EventFiles as f3 where f3.EventID = e.EventID and f3.fileID =3) FROM Events e Method 2 - without subqueries CREATE TEMPORARY TABLE FileOrder( EventID tinyint, EventName char(20), File1 varchar(35), File2 varchar(35), File3 varchar(35)); INSERT INTO FileOrder (select Events.EventID,EventName, FileName,'None','None' FROM EVENTS INNER JOIN EventFiles on EventFiles.EventID = Events.EventID Where FileID = 1) UPDATE FileOrder SET File2 = (SELECT DISTINCT FileName FROM EventFiles,Events WHERE EventFiles.FileID = 2 AND EventFiles.EventID = FileOrder.EventID); UPDATE FileOrder SET File3 = (SELECT DISTINCT FileName FROM EventFiles,Events WHERE EventFiles.FileID = 3 AND EventFiles.EventID = FileOrder.EventID); This approach won't give you a row for Event 3 since it doesn't exist in EventFiles. I'm sure this can be solved with a little work. Sorry I didn't you the same column names as you did, but I was in a hurry. A report writer or even ACCESS makes these cross-tab reports trivial. Regards Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
