2

I need to use Joins for a project but I have forgot how to use it. It's all just messing with my head now, I can't quite figure it out.

I have five tables, all of which I need to join: 

categories - id, name
movies_categories - movie_id, category_id
movies - id, title, description, release_date, cover_image
users - id, username, password
user_movies - user_id, movie_id, favorite, review, watch_date, rating

As you can see the project is movie related, and user_movies contains all the movies the user has watched. What I need to do is get all the movies a certain user has watched. So, I need all of the movie info for each movie, the username (that's pretty much all the info I need from the user), and some of the columns from user_movies (all of them except the first two). I also need to combine all of the categories for each movies into one "variable". I believe you can use GROUP_CONCAT() to do that, I've done it before.

It's a tricky task, I really need to get my head around this. If anyone can help me out I'd appreciate it.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
qwerty
  • 5,166
  • 17
  • 56
  • 77

1 Answers1

6

If you had provided table schemas (create table scripts) I could test query on sqlfiddle so I couldn't test but I think this query is what you need.

select u.username, u.password
     , um.user_id, um.movie_id, um.favorite
     , um.review, um.watch_date, um.rating 
     , m.movie_title, m.description, m.release_date, m.cover_image
     , mc.category_id
     , group_concat(distinct c.name) as movie_categorynames
from user_movies um
inner join users u on u.id = um.user_id
inner join movies m on m.id = um.movie_id
inner join movies_categories mc on mc.movie_id = um.movie_id
inner join categories c on c.id = mc.category_id
where um.user_id=1
group by um.user_id



USERNAME    PASSWORD    USER_ID MOVIE_ID    FAVORITE    REVIEW  WATCH_DATE  RATING  MOVIE_TITLE DESCRIPTION RELEASE_DATE    COVER_IMAGE CATEGORY_ID MOVIE_CATEGORYNAMES
nike    5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8    1   3   0   This is my movie review!    51351   7   It's Kind of a Funny Story  A clinically depressed teenager gets a new start after he checks himself into an adult psychiatric ward.    2010    8d27232902780886db032afb8d4883a7.jpg    5   Comedy,Drama
Nesim Razon
  • 9,684
  • 3
  • 36
  • 48
  • 1
    You may want to code it as "group_concat(distinct c.name) as movie_categorynames" if you want to discard duplicates. – David Gorsline Apr 20 '12 at 19:06
  • Thanks a bunch! I got an error when i ran the code "Unknown column 'm.title' in 'field list'". If you can try fixing it it would be great. Here's a dump the table schemas: http://pastebin.com/6Qe68zMp Thanks again! – qwerty Apr 20 '12 at 19:55
  • It is because your question says your movie table has a field title but the schema you pasted on pastebin says field name is movie_title. Change m.title to m.movie_title. (edited my query) – Nesim Razon Apr 20 '12 at 20:19