MySQL 'multiple rows' from two joins challenge

Recardo Patrese Published at Dev

Recardo Patrese

I have the following MySQL query joining two additional tables..

SELECT  plants.plant_id,
        plants.scientific_name,
        plants_features.features,
        plants_features.features_id

FROM    plants_features,
        plants2features,
        plants

WHERE   plants_features.features_id = plants2features.features_id

AND     plants2features.plant_id = plants.plant_id

and I was happy with the output.

plant_id,scientific_name,features,features_id
3,"Actaea matsumurae 'White Pearl' (Bugbane)","colourful fruit",6
11,"Heuchera 'Beauty Colour' (Coral bells)","salt resistant/coastal",15
18,"Phyllostachys nigra (Black bamboo)","colourful bark",5
26,"Carex morrowii 'Silver Sceptre' (Japanese sedge)","drought tolerant",18
27,"Heuchera 'Obsidian' (Coral bells)","salt resistant/coastal",15
29,"Dianella tasmanica 'Tas Red' (Flax lily)","drought tolerant",18
38,"Stipa tenuissima (Mexican feather grass)","attractive seed-heads",2
38,"Stipa tenuissima (Mexican feather grass)","invasive/self seed/suckering",13

You can see from the plant_id '38' (bottom two rows) that it's outputting more than one row per record.

My question is guys, can you please advise what exactly the new MySQL query needs to be to ensure multiple rows are in one record by 'plant_id'?

Thanks in advance, Richard.

Lukasz Szozda

You need grouping and aggregation functions. You can use GROUP_CONCAT to get comma separated list:

SELECT  plants.plant_id,
        plants.scientific_name,
        GROUP_CONCAT(plants_features.features) AS features,
        GROUP_CONCAT(plants_features.features_id) AS feature_ids
FROM plants_features
JOIN plants2features
  ON plants_features.features_id = plants2features.features_id
JOIN plants
  ON plants2features.plant_id = plants.plant_id
GROUP BY plants.plant_id, plants.scientific_name;

Also use JOIN syntax instead of comma and where.

Collected from the Internet

Please contact [email protected] to delete if infringement.