Subquery field as an array
This is a discussion on Subquery field as an array within the MySQL Database forums, part of the Database Forums category; I have a database where the Event_Items table is a zero to many relationship to Events table. Attempting to run ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-27-2007
|
|||
|
|||
Subquery field as an array
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 
|
|
03-27-2007
|
|||
|
|||
|
Re: Subquery field as an array
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
|
|
03-27-2007
|
|||
|
|||
|
Re: Subquery field as an array
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 > Thanks Group_Concat is a new one for me, but appears to be exactly what I want. Thank you!
|
|
03-27-2007
|
|||
|
|||
|
Re: Subquery field as an array
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
|
Linear Mode
