MySQL joins with one to many table relationships

Question

I have no idea if this is possible but is there a way in MySQL to produce a single query where the multiple results of a one to many table join can be set as an array on a key of the result for the one item?

I realise that question isn't very clear so I'll explain what I'm after further:

Firstly, I'm currently using implicit joins and would like to learn more on explicit joins (of which I currently know very little), perhaps these could provide the answer I'm looking for?

For example given two tables:

CREATE TABLE `a` (
    `id_a` int(11) NOT NULL AUTO_INCREMENT,
    `a_column1` varchar(255) NOT NULL,
    ...
    PRIMARY KEY (`id_a`)
)

CREATE TABLE `b` (
    `id_b` int(11) NOT NULL AUTO_INCREMENT,
    `id_a` int(11) NOT NULL,
    `b_column1` varchar(255) NOT NULL,
    ...
    PRIMARY KEY (`id_b`)
)

Where table b has many entries related to a single entry in table a.

If I were to run the following query:

SELECT a.*, b.* FROM a, b WHERE b.id_a = a.id_a AND a.id_a = x;

I would get an array with multiple entries with the data of the single item id x repeated. What I actually want is a single row returned from table a with a key defined as b which contains an array of the multiple matching entries from table b. I suspect that this is not possible with a query alone, but it would be great if it was. Currently I am doing the following in PHP (where $this->_db is a Zend Framework database adapter). This runs a lot of queries!:

$query = "SELECT * FROM a WHERE id_a = ?";
$items = $this->_db->fetchAll($query, $id);

foreach($items as $key => $item) {
    $query = "SELECT * FROM b WHERE id_a = ?";
    $items[$key]['b'] = $this->_db->fetchAll($query, $item['id']);
}

Alternatively I can use my original join query and post process, which I suspect is more efficient, but means I need to explicitly copy over the columns I need (a pain and far from elegant):

$query = "SELECT * FROM a, b WHERE a.id_a = b.id_a AND a.id_a = ?";
$items = $this->_db->fetchAll($query, $id);
$output = array('a_column1' => $items[0]['a_column1'], etc...);
$output['b'] = array();

foreach($items as $item) {
    $b = array('b_column1' => $item['b_column1'], etc...);
    $output['b'][] = $b;
}

score 1 · Accepted Answer · answered Nov 05 '11 at 23:50

Your query that uses implicit JOIN:

SELECT a.*
     , b.* 
FROM a, b 
WHERE b.id_a = a.id_a 
  AND a.id_a = x

With explicit JOIN:

SELECT a.*
     , b.* 
FROM a
  JOIN b
    ON b.id_a = a.id_a 
WHERE a.id_a = x

One way to have the data in one query is to use the GROUP_CONCAT() function. But it may not be in a format you can use:

SELECT a.*
     , GROUP_CONCAT( b.id_b
                     ORDER BY b.id_b ASC
                     SEPARATOR ',' 
                   ) AS b_ids
     , GROUP_CONCAT( b.b_column1 
                     ORDER BY b.id_b ASC
                     SEPARATOR ',' 
                   ) AS b_column1s
     , ...                            --- etc
FROM a
  JOIN b
    ON b.id_a = a.id_a 
WHERE a.id_a = x
GROUP BY a.id_a

Thank you, that's exactly what I'm after. – baseten Nov 06 '11 at 16:45 — baseten, Nov 06 '11 at 16:45

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

You're probably looking for an ORM (object-relational mapper), which would handle associations between objects and would be able to return one A object containing an array of B objects.

See Good PHP ORM Library?

Using explicit joins, the query would look like this:

SELECT a.*, b.* FROM a inner join b on b.id_a = a.id_a where a.id_a = x;

edited May 23 '17 at 12:03

Community

1
1

answered Nov 05 '11 at 21:19

JB Nizet

678,734
91
1,224
1,255

Thank you, I think an ORM library is too much for this particular project, but it's definitely something I'll be researching further. Thanks also for the explicit join query. – baseten Nov 06 '11 at 16:45

MySQL joins with one to many table relationships

2 Answers2