CODEWITHSUNDEEP
phpmysqlsql
krishna77
krishna77

Reputation: 33

How to avoid the repetitions of values when using Sql join

i would like to know how can i avoid the repetitions of values when i'm using SQL join example:

Table: Users
[id]    [name]
1       Bob
2       Junior

Table: images
[id]    [img]       [user_id]
1       water.jpg   1
2       spoon.jpg   2
3       beer.jpg    1

how can i get these values from the database:

id      =>  1
name    =>  Bob
img     =>  [water.jpg, beer.jpg]

id      =>  2 
name    =>  Junior
img     =>  [spoon.jpg]

Instead of

id      =>  1
name    =>  Bob
img     =>  water.jpg 

id      =>  1
name    =>  Bob
img     =>  beer.jpg

id      =>  2
name    =>  Junior
img     =>  spoon.jpg

i'm using MySQL what query to this?

Upvotes: 1

Views: 41

Answers (2)

Danilo Bustos
Danilo Bustos

Reputation: 1093

try this:

    SELECT users.id, users.name, concat('[',GROUP_CONCAT(images.img SEPARATOR ','),']') as images_concat
    FROM users 
    INNER JOIN images ON (users.id = images.user_id)
    GROUP BY users.id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270201

You can use group by and group_concat():

select u.id, u.name, group_concat(i.img) as imgs
from users u join
     images i
     on u.id = i.user_id
group by u.id;

Upvotes: 2

Related Questions