doubleJ - 7:55 pm on Jun 26, 2012 (gmt 0)

Hello...
I have 3 sql tables for event information.
1 table is for the venue (address, phone, etc...).
1 table for the actual event information (speaker, date, etc...).
1 table is an intermediate "join" table (1 venue could be used for multiple events and events could span multiple days or have multiple sessions per day).

Table example...

TblSermon:
ID Date Time Speaker
----------------------------------
122 2012-09-18 "09:00 AM" "Joshua Jackson"
123 2012-09-18 "11:00 AM" "Joshua Jackson"
1452 2014-02-25 "06:00 PM" "JoJo The Idiot Circus Boy"

TblLocation:
ID Location City State
-------------------------------------------
14 "Omega Arena" Boston MA
1263 "Kentucky Civic Center" Lexingon KY

TblJoinSermonLocation:
ID MeetingName Location Sermon
-----------------------------------
323 "Special Times" 14 122
324 "Special Times" 14 123
345 "Journeys" 1263 1452


Query example...

SELECT TOP (100) PERCENT dbo.TblSermon.Date, dbo.TblSermon.Time, dbo.TblSermon.Speaker, dbo.TblJoinSermonLocation.MeetingName, dbo.TblLocation.Location, dbo.TblLocation.City, dbo.TblLocation.State
FROM dbo.TblLocation RIGHT OUTER JOIN dbo.TblJoinSermonLocation ON dbo.TblLocation.ID = dbo.TblJoinSermonLocation.Location RIGHT OUTER JOIN dbo.TblSermon ON dbo.TblJoinSermonLocation.Sermon = dbo.TblSermon.ID
WHERE (dbo.TblSermon.Date >= { fn NOW() })
ORDER BY dbo.TblSermon.Date, dbo.TblSermon.Time


Obviously, this will list all information (Date, Time, Speaker, MeetingName, Location, City, State) for each record that matches.
What I'm looking for is the ability to show each venue (Location) once and then list all the dates/times associated with that event, under it.
There's no point in duplicating MeetingName, Location, City, State for each record.

Array example...

Array
(
[Location] => Omega Arena
[City] => Boston
[State] => MA
[MeetingName] => Special Times
[Date] => 2012-09-19 00:00:00.000
[Time] => 09:00 AM
[Speaker] => Joshua Jackson
)
Array
(
[Location] => Omega Arena
[City] => Boston
[State] => MA
[MeetingName] => Special Times
[Date] => 2012-09-19 00:00:00.000
[Time] => 11:00 AM
[Speaker] => Joshua Jackson
)


My theory is that I can create a multidimensional array where the first level is the venue info (Location, City, State), the second level is the event info (MeetingName), and the third are the dates/times).

Array example...

Array
(
[Location] => Omega Arena
[City] => Boston
[State] => MA
Array
(
[MeetingName] => Special Times
Array
(
[Date] => 2012-09-19 00:00:00.000
[Time] => 09:00 AM
[Speaker] => Joshua Jackson
)
Array
(
[Date] => 2012-09-19 00:00:00.000
[Time] => 11:00 AM
[Speaker] => Joshua Jackson
)
)
)


Unfortunately, I haven't figured out how to do that.
Hehehe...
Can you guys help me out?
BTW...
How do you indent, here?
JJ