CODEWITHSUNDEEP
mysqlsql
lastpeony4
lastpeony4

Reputation: 577

MySQL Simple Join Avoid multiple duplicate rows

Lets say that i have 2 tables: 1st has following columns:

products
--------
id   name        price
1  someproduct  99

2nd

productimages
-----------
productId img
1          someimgurl
1          someimgurl2

I would like to get name,price and images of product 1.

SELECT products.name, products.price, productimages.img  FROM products INNER JOIN productimages WHERE products.id=1

This query gives me following result:

[ {
    name: 'someproduct',
    price: 99,
    img:
     'someimg' },
   {
    name: 'someproduct',
    price: 99,
    img:
     'someimgurl2' }
]

As you see name and price are repeated.What i am trying to get as result is this:

[ {
    name: 'someproduct',
    price: 99,
    img:[
     'someimgurl','someimgurl2'] },
]

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use aggregation to get a single row. Perhaps:

SELECT p.name, p.price, GROUP_CONCAT(pi.img) as img
FROM products p INNER JOIN
     productimages pi
     on p.id = pi.product_id
WHERE p.id = 1
GROUP BY p.name, p.price;

I also notice that you had a JOIN with no ON clause. This would be a syntax error in any database other than MySQL.

Upvotes: 1

Related Questions