MySQL querying a junction table returns duplicate rows

Question

I have the following table structure:

So a product may have multiple product_type's.

When I do my Join in MySQL I get repeated product records for each product_type. So for example if I have product called Actifoam and it has 2 records in the products_types table then the result of the query will include Actifoam twice (each one with a different product_type.

Here is my query:

SELECT DISTINCT product.*, product_type.name as product_type_name
FROM products_types
JOIN product_type ON product_type.id = products_types.product_type_id
JOIN product ON product.id = products_types.product_id

Here is what the result of the query shows:

[
    0 => [
        'id' => 'recccAQHxsb4OEsX6'
        'name' => 'Actifoam'
        'product_type_name' => 'Silver Dressing'
    ]
    1 => [
        'id' => 'recccAQHxsb4OEsX6'
        'name' => 'Actifoam'
        'product_type_name' => 'Foam'
    ]
]

I don't want the product records to appear multiple times like this, I would like everything to appear on a single row if there are multiple product_types.

Anyone have an idea how I can achieve this?

score 2 · Accepted Answer · answered Aug 17 '17 at 09:21

DISTINCT will return unique rows only if 2 rows have exact same corresponding values for all columns in SELECT statement. In your case you need GROUP BY and GROUP_CONCAT.

GROUP_CONCAT will return product_type_name separated by comma.

SELECT product.*, GROUP_CONCAT(product_type.name) as product_type_name
FROM products_types
JOIN product_type ON product_type.id = products_types.product_type_id
JOIN product ON product.id = products_types.product_id
GROUP BY product.id

MySQL querying a junction table returns duplicate rows

1 Answers1