Professional Web Applications Themes

Subquery field as an array - MySQL

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 ...

  1. #1

    Default 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
    totalstranger Guest

  2. #2

    Default Re: Subquery field as an array

    On 27 Mar, 16:15, totalstranger <yahoo.net> wrote: 

    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

    Captain Guest

  3. #3

    Default Re: Subquery field as an array

    On or about 3/27/2007 11:22 AM, it came to pass that Captain Paralytic
    wrote: 
    >
    > 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
    >[/ref]
    Thanks Group_Concat is a new one for me, but appears to be exactly what
    I want.

    Thank you!
    totalstranger Guest

  4. #4

    Default Re: Subquery field as an array

    On or about 3/27/2007 11:22 AM, it came to pass that Captain Paralytic
    wrote: 
    >
    > 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
    >[/ref]
    This query did exactly what I wanted
    SELECT Id, (SELECT Group_Concat( Band_Id )FROM Event_Items
    WHERE Event_id = Events.Id) FROM Events
    totalstranger Guest

Similar Threads

  1. When you bind an array to a datagrid, what's the field name?
    By Jon Edney in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: October 5th, 01:37 PM
  2. field in an array
    By _mvm in forum Macromedia ColdFusion
    Replies: 2
    Last Post: August 17th, 05:02 PM
  3. some problem to add a mysql field value to an array
    By SAN in forum PHP Development
    Replies: 2
    Last Post: December 21st, 05:42 PM
  4. how do you access an array in an included/required field?
    By NotGiven in forum PHP Development
    Replies: 3
    Last Post: November 30th, 11:12 AM
  5. Array as a field??
    By Manny Winebreg in forum PHP Development
    Replies: 0
    Last Post: September 4th, 05:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139