1

I have the following tables:

Table categories {
  -id-       -name-
  1          Action
  2          Comedy
  4          Drama
  5          Dance
}

Table movies {
  -id-       -name-      -description-
   1         Example 1   Movie description 1
   2         Example 2   Movie description 2
   4         Example 3   Movie description 3
}

Table movies_categories {
  -movie_id-     -category_id-
  1              2
  2              1
  4              3
}

I want to select everything from the movies table, and also get the categories for that movie concatenated in one column separated by a comma (or whatever, separated by something).

I gave it a shot myself but I was not able to concatenate the categories (right now it just selects the first category and ignores the rest), and I was also not able to add a WHERE clause, which i really need. I just got a syntax error, any ideas why?

SELECT movies.*, categories.name FROM movies LEFT JOIN movies_categories ON (movies.id = movies_categories.movie_id) LEFT JOIN categories ON (movies_categories.category_id = categories.id)
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Katie
  • 173
  • 1
  • 1
  • 6

1 Answers1

1

You can use GROUP_CONCAT:

SELECT movies.*, GROUP_CONCAT(categories.name)
FROM movies
LEFT JOIN movies_categories ON (movies.id = movies_categories.movie_id)
LEFT JOIN categories ON (movies_categories.category_id = categories.id)
GROUP BY movies.id
robbrit
  • 17,560
  • 4
  • 48
  • 68
  • Awesome! I knew i could utilize group_concat() but i didn't know how. The only thing left now is how to add a WHERE clause to it? I still can't make it work. I want to select by movie ID. So... `SELECT movies.*, GROUP_CONCAT(categories.name) FROM movies WHERE id='6'` should work, right? No? – Katie Jul 28 '11 at 17:11
  • Yep, provided you put those JOINs back in. If you're selecting on the movie ID you don't need the GROUP BY clause. – robbrit Jul 28 '11 at 17:12
  • Could you give me an example? That would be great! I'm still kind of a newbie with joins. – Katie Jul 28 '11 at 17:16
  • Just take the example from my original answer and replace the GROUP BY movies.id with WHERE id = '6'. – robbrit Jul 28 '11 at 17:18
  • Thanks, works now! I tried putting it before the joins, that's why it didn't work. Marked as accepted. – Katie Jul 28 '11 at 17:21