On or about 3/27/2007 11:22 AM, it came to pass that Captain Paralytic
wrote:
> On 27 Mar, 16:15, totalstranger <totalstran...@not.yahoo.net> wrote:
>> I have a database where the Event_Items table is a zero to many
>> relationship to Events table.
>>
>> Attempting to run this query
>>
>> Select Id,(select Band_Id from Event_Items where Event_Id = Events.Id)
>> FROM `Events`
>>
>> results in an error message #1242 - Subquery returns more than 1 row
>>
>> I understand the error, however I want to get the Band_Id field result
>> to be an array or table that can be iterated. I remember doing something
>> like this in Oracle or perhaps DB2 but can't remember how. I've
>> attempted without success to use the INTO syntax for example:
>>
>> Set @AnArray = 0;
>> Select Id,(select Band_Id into @AnArray from Event_Items where Event_Id
>> = Events.Id) FROM `Events`
>>
>> Problem is @AnArray is not an array but a column
>>
>> I'm open to suggestions but want to keep this as a single query and not
>> break it into two SQl statements or use a join that forces all fields
>> for Events to be selected for each row in Event_Items. The MySql version
>> is 4.1.20
>>
>> Thank you
>
> A join does not force all fields to be selected. You list only the
> required fields from each table. This is the efficient way to do it.
> Also check out the GROUP_CONCAT() function
>
This query did exactly what I wanted
SELECT Id, (SELECT Group_Concat( Band_Id )FROM Event_Items
WHERE Event_id = Events.Id) FROM Events