MySQL Simple Join Avoid multiple duplicate rows

Question

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'] },
]

Gordon Linoff · Accepted Answer · 2019-01-24 19:41:18Z

1

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.

answered Jan 24, 2019 at 19:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lastpeony4 Over a year ago

this concats 2 images into one and seperates with , is it possible to make each image element of img array ? so that img[0] = someimgurl and img[1] = someimgurl2 or am i wrong ?

Uueerdo Over a year ago

@lastpeony4 that is not possible, mysql returns complete rows of fields, and fields can only hold single values.

lastpeony4 Over a year ago

so what is the proper way to achieve this ? am i gonna seperate them on server side before sending data back to client ?

Gordon Linoff Over a year ago

@lastpeony4 . . . There is no such thing as an array in MySQL. You need to parse the resulting string into an array at the application level.

Collectives™ on Stack Overflow

MySQL Simple Join Avoid multiple duplicate rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related