[thelist] SQL question - concatenating multiple records

Matthew Bernhardt bernhardt.7 at osu.edu
Mon Aug 6 08:52:38 CDT 2007


Hello everyone,
   I ran into a challenge over the weekend that I wonder if anyone here can  
help with. Briefly, I was trying to write a query to return all broadcasts  
of a given event, with the broadcasting stations in a single field.
   The schema I'm working with, abbreviated, looks like this:

tbl_event
-=-=-
ID (int)
EventName (varchar)

lnk_event_tv
-=-=-
ID (int)
EventID (int)
StationID (int)
BroadcastTime (datetime)

tbl_television
-=-=-
ID (int)
Name (varchar)

So, I can fairly easily write a SQL statement like the following:

SELECT tbl_event.ID, tbl_event.EventName, tbl_television.Name
 FROM tbl_event
LEFT OUTER JOIN lnk_event_tv ON tbl_event.ID = lnk_event_tv.EventID
LEFT OUTER JOIN tbl_television ON lnk_event_tv.StationID =  
tbl_television.ID

However, if a given event is broadcast on two different stations, it's  
going to have two records in lnk_event_tv - and thus two records in the  
result.

My question, then is: is it possible to write this query in a way that  
groups the tbl_television.Name results for each event into one field? Or  
should I do that with the PHP code?

Thanks,
Matt
bernhardt.7 at osu.edu



More information about the thelist mailing list