RE: Query to return Multiple values in a field?

[Jerry Schwartz]

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]

Re: Query to return Multiple values in a field?

[Jesse]

I'm not sure this will work for you, but look at the GROUP BY clause and
the
GROUP_CONCAT() function.


That was exactly what I needed!  I converted my query as follows, and I'm
getting exactly what I was looking for:

SELECT S.*, CONCAT(C.FirstName,' ',C.LastName) AS CounselorName,
  (SELECT Count(*) FROM CamperActivitySelections WHERE SectionID=S.ID)
AS Cap,
  CONVERT(GROUP_CONCAT(StartDateTime ORDER BY StartDateTime SEPARATOR
'br'),Char) As Sh
FROM Sections S
  LEFT JOIN Counselors C ON C.ID=S.CounselorID
  LEFT JOIN (SELECT SectionID,StartDateTime FROM SectionSchedule SS) As SS
ON SS.`SectionID`=S.ID
WHERE ActivityID=65
GROUP BY S.ID

THANKS FOR THE HELP!

Jesse 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]