SQL Statement Problem - Combine several rows in a join

[eluser]wiZe[/eluser]
hey there,

i got stuck with one of my SQL statements and need some help. the thing i want to achieve is the following:

there is one groups table which stores all the basic data, which is independant from the language, and one groups_data table which contains the language specific data of each group.

now i want to select my group from the groups table and join it with all available languages from the groups_data table in one statement, so i have all my groups like "laptop, pc, phone" associated with their available languages.

i would like to end up with an array like this:

Code:

Array
(
    [group1_index] => Array
        (
            [group_id] => 1
            [group_unspecific_data] => My Data
            [group_languages] => Array
                (
                    [0] => "de"
                    [1] => "en"
                )
        )

    [group1_index] => Array
        (
            [group_id] => 2
            [group_unspecific_data] => My other Data
            [group_languages] => Array
                (
                    [0] => "de"
                    [1] => "en"
                    [2] => "fr"
                )
        )
    [group...] => ...
)

is it possible to combine several rows with a particular attribute (e.g. the group_id which reffers to the non specific group table) in a join statement? i really appreciate any hints and help, thanks in advance!

p.s.: i will use the active record pattern, but i dont even know the sql syntax for such an opertaion.

edit: i attached an image with a demo of how these tables could look like.

[eluser]obiron2[/eluser]
You basically have two choices.

1) Select all the records using a join between group and language to return a flat dataset and then process into an 2 dimensional array closing the inner array on change of group.

2) One query that gets the groups you are interested in and then a for..each loop that gets the languages for the group ID.

What you actually need is rolled up data, but I don't think that MySQL or PHP support the process.

Obiron

[eluser]wiZe[/eluser]
thanks for reply obiron2. yea i thought so too, but i had a little hope i could do it with an efficiant query. think i go with the first one, instead of fireing thousands of queries in a foreach loop!

€: i figuered out how to get it right now. i think it's the best to grab all the data you need out of the database and then build your array the way you want it. it's no problem with php, i just thought there might be a on-the-fly solution within the sql query...