I can not understand how to make a request so that the bloody ( WHERE genre_id = 1 ) did not cut the array in GROUP_CONCAT .

General view of the tables:

 idbook book_id | genre_id idgenre | genre

This query works fine:

 SELECT GROUP_CONCAT(genre) AS thisgenre, FROM book LEFT JOIN genre_book ON idbook = book_id LEFT JOIN genre ON genre_id = idgenre GROUP BY idbook

Displays:

 Π’Ρ€ΠΈΠ»Π»Π΅Ρ€ Π’Ρ€ΠΈΠ»Π»Π΅Ρ€,Экшн,ΠŸΡ€ΠΈΠΊΠ»ΡŽΡ‡Π΅Π½ΠΈΡ Π’Ρ€ΠΈΠ»Π»Π΅Ρ€,ΠŸΡΠΈΡ…ΠΎΠ»ΠΎΠ³ΠΈΡ,ΠŸΠΎΡΡ‚Π°ΠΏΠΎΠΊΠ°Π»ΠΈΠΏΡ‚ΠΈΠΊΠ°,УТасы

And when I try to add a search query to genres ( WHERE genre_id = 1 ):

 SELECT GROUP_CONCAT(genre) AS thisgenre, FROM book LEFT JOIN genre_book ON idbook = book_id LEFT JOIN genre ON genre_id = idgenre WHERE genre_id = 1 GROUP BY idbook

It turns out:

 Π’Ρ€ΠΈΠ»Π»Π΅Ρ€ Π’Ρ€ΠΈΠ»Π»Π΅Ρ€ Π’Ρ€ΠΈΠ»Π»Π΅Ρ€

But I need the whole array of genres, I bring it to the page.

  • Maybe you need to write LEFT JOIN genre ON genre_id = idgenre AND genre_id = 1 ? It would certainly be nice to see test data for example in sqlfiddle.com for a greater understanding of the structures - Alexey Shimansky

1 answer 1

Leave all genres in the sample to capture group_concat and filter the required books after grouping, i.e. in having :

 SELECT GROUP_CONCAT(genre) AS thisgenre, FROM book LEFT JOIN genre_book ON idbook = book_id LEFT JOIN genre ON genre_id = idgenre GROUP BY idbook HAVING sum(IF(genre_id=1,1,0))>0