Many-to-many SQL relation: how to retrieve all related entries in one query?

Question

Hi Guys!

I have the same tables as in this question: http://stackoverflow.com/questions/1202978/mysql-query-over-many-to-many-relationship. What I'm struggling with, though, is how to construct a query which would obtain a plant with all its attributes. I.e. if I want P1 I get

P1 | A1 | A2 | A3 or P1 | A1, A2, A3

As opposed to

P1 | A1
P1 | A2
P1 | A3

if I want all plants, I get

P1 | A1 | A2 | A3          P1 | A1, A2, A3
P2 | A1 | A2         or    P2 | A1, A2
P3 | A2 | A3               P3 | A2, A3

And the other way round as well, that is display all plants for a given parameter. First of all - is it possible to create such an SQL query?

What I have at the moment is I obtain each plant once and for each row I access the database again asking for attributes. As you probably realise it's very inefficient so I hope you can point me in the right direction.

EDIT: I'd like to get one plant per one row and all the attributes in that same row.

Answer 1

simply join them:

    select p.*, a.*
      from plants p
inner join plantattributes pa
        on p.plantid = pa.plantid
inner join attributes a
        on pa.attrid = a.attrid
     where pa.plantid = ?

if you want all plants for a given attribute use where a.attrid = ? instead

Answer 2

I think that you should use database procedure instead of query, but I dunno mysql procedures so well, so I can`t help ya with this. But i hope this will give you new direction of thinking and maybe you will find solution :)

Answer 3

Do you mean something like the following? I've simplified things a little by removing the link table, but the idea is the same:

SELECT
  plant.name,
  GROUP_CONCAT(attrib.name) AS attribs
FROM plant
JOIN attrib ON attrib.plant_id = plant.id
GROUP BY plant.id;

+------+----------------+
| name | attribs        |
+------+----------------+
| P1   | A1-1,A1-2,A1-3 |
| P2   | A2-1,A2-2,A2-3 |
| P3   | A3-1,A3-2,A3-3 |
+------+----------------+

Or this:

SELECT plant.name, GROUP_CONCAT(attrib.name)
FROM plant JOIN attrib
ON attrib.plant_id = plant.id
WHERE attrib.name IN ('A2-1', 'A2-3', 'A3-2')
GROUP BY plant.id;

+------+---------------------------+
| name | GROUP_CONCAT(attrib.name) |
+------+---------------------------+
| P2   | A2-1,A2-3                 |
| P3   | A3-2                      |
+------+---------------------------+

The above is taken from the following inserts:

INSERT INTO plant (name) VALUES
  ('P1'), ('P2'), ('P3');

INSERT INTO attrib (plant_id, name) VALUES
  (1, 'A1-1'), (1, 'A1-2'), (1, 'A1-3'),
  (2, 'A2-1'), (2, 'A2-2'), (2, 'A2-3'),
  (3, 'A3-1'), (3, 'A3-2'), (3, 'A3-3');

Answer 4

You've tagged this PHP, so let's use PHP to solve the problem. Where $db is a PDO connection, and the table structure is as defined in knittl's comment:

$plant_id = 1;
$plant_attributes = array();
$query = "knittl's query from above, because it works well enough for us.";
$sth = $db->prepare($query);
$sth->execute($plant_id);
while($row = $sth->fetch(PDO::FETCH_ASSOC))
    $plant_attributes[] = $row['attrid'];

Tada! You now have a list of all the attributes for the selected plant. For all plants:

$plant_attributes = array();
$query = "knittl's query from above, minus the WHERE clause";
$sth = $db->prepare($query);
$sth->execute();
while($row = $sth->fetch(PDO::FETCH_ASSOC))
    $plant_attributes[ $row['plantid'] ][] = $row['attrid'];

Tada! You now have a list of all plants and their attributes.

The thing you originally asked for is similar to a "pivot table" or "crosstab." MySQL can't do what you've asked natively. It might be possible to create a stored procedure tries to return the data in a tabular format instead of a column-oriented format as it exists in the database, but that's an incredible pain. This, on the other hand, is not an incredible pain.

Answer 5

This query will return all plants together with their attributes. Tables as in this question http://stackoverflow.com/questions/1202978/mysql-query-over-many-to-many-relationship.

SELECT `Plant`.`name`,
GROUP_CONCAT(`Attribute`.`name`) AS attribs
FROM `plant`, `attribute`, `hasAttribute`
WHERE `Plant`.`pid` = `hasAttribute`.`pid`
AND `Attribute`.`aid` = `hasAttribute`.`aid`
GROUP BY `Plant`.`name`

Should return:

+------+----------+
| name | attribs  |
+------+----------+
| P1   | A1,A2,A3 |
| P2   | A1,A2    |
| P3   | A2,A3    |
+------+----------+