MySQL query - getting a list of 'extra' information from JOIN table similar to a nested array

This was a bit difficult to explain in the title, but I should be able to here. I have two tables that look like this:

Table 1:
    -id
    -created
    -last_modified
    -title

Table 2:
    -id
    -parent_id
    -type
    -value

The structure is somewhat akin to the following: an item from table one can have many attributes associated with it. Each attribute is listed in the second table, with a reference back to the original.

The issue I have, is that I w开发者_开发百科ant to be able to get a list of records from table 1 to display in a table (using pagination), but also want to be able to retrieve all the attributes from Table 2 associated with each Table 1 record at the same time, so that I might have the following:

(Table 1) ID1 [Title] has attributes x, y, z
(Table 1) ID2 [Title] has attributes x, y, z
(Table 1) ID3 [Title] has attributes x, y, z

and so on. Ideally I would like to be able to associate each attribute with its type as well...currently with a join I receive multiple rows of the same records (with the joined data different each time), and grouping them together removes some of the joined data entirely.

Essentially what I'm after is an array of attributes to be returned for each record from Table 1 (in some sort).

I'm thinking of using MongoDB for this project as I know I can do it simply with that, but I'm trying to do it with MySQL as that is what the existing platform is using.

I hope I've made sense with what I'm asking :) Any help would be appreciated!

Dan

Sounds like more of a display problem. The joined query is the best way to go. You'd then just have a simple loop in your retrieve/display code to check for when you transition from one Table1 record to another and adjust the output as necessary.

You could retrieve all the child records as single fields using MySQL's group_concat() function, but then you just end up with (basically) a monolithic string of concatenated data, and not the individual records the joinedquery/display loop will provide. group_concat also has length-limits on how much data it'll return (1024 bytes by default), which can be easily hit with large data sets.