I'm not sure this will work for you, but look at the GROUP BY clause and the GROUP_CONCAT() function.
Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: Jesse [mailto:[EMAIL PROTECTED] > Sent: Friday, April 06, 2007 1:17 PM > To: MySQL List > Subject: Query to return Multiple values in a field? > > Using MySQL 5.0.22-community-nt-log > > Is there a way to compose a query that would show multiple > values in field? > In other words, I have a table structure like this: > > Activity 1 > Section 1 > Schedule 1 > Schedule 2 > Section 2 > Schedule 1 > Activity 2 > Section 1 > Schedule 1 > Schedule 2 > > So, I would like to return a query that shows the activity > name, section #, > and in a single field, all the schedule entries. Just to see > if it would > work, I did the following query: > > SELECT S.*, CONCAT(C.FirstName,' ',C.LastName) AS CounselorName, > (SELECT Count(*) FROM CamperActivitySelections WHERE > SectionID=S.ID) > AS Cap, > (SELECT StartDateTime FROM SectionSchedule SS WHERE > SS.SectionID=S.ID) As Sh > FROM Sections S LEFT JOIN Counselors C ON C.ID=S.CounselorID > WHERE ActivityID=65 > > This works fine if there is only one schedule entry. > However, when there > are two schedule entries or more, it returns an empty data > set. If there > were some way to get the results and add them all together, > separated by a > "<br>", that would be perfect. Just make that the field > value, which I > could then display. The only other alternative is to put this > into a loop in > my program and create a query for each row to get the > schedule information. > > Any help is appreciated. > > Thanks, > Jesse > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
