user3574492 user3574492 - 1 year ago 62
MySQL Question

MySQL querying a junction table returns duplicate rows

I have the following table structure:
enter image description here

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?

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download