Sql LEFT JOIN returns multiple rows-CodePudding

this is my query

SELECT items.title
, genre.genre 
FROM `items` 
LEFT JOIN `genre` 
ON genre.item_id = items.item_id

it returns result as

Title Genre
Title1 Genre1
Title1 Genre2
Title2 Genre2

but i want result as

Title Genre
Title1 Genre1,Genre2
Title2 Genre2

UPDATE

my items table Items table my genre table genre table

"SELECT items.title, GROUP_CONCAT(genre.genre) as genres FROM `items` LEFT JOIN `genre` ON genre.item_id = items.item_id"

it now returns

Title1 Genre1,Genre2

but my second row of items table does not have any row in genre table.But it should return second row too as i am using LEFT JOIN. but it does not return second row

CodePudding user response：

This should work:

SELECT i.title, GROUP_CONCAT(g.genre)
FROM items i
LEFT JOIN genre g ON g.item_id = i.item_id
GROUP BY i.title

Note that I used table aliases here, but they're not needed (they just arguably make the query a bit cleaner).