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