1

I am using MYSQL and here is the schema that I have:

Here is my first table user

+-----+------------------+
| id  | name             |
+-----+------------------+
| 101 | Daniel Radcliffe |
| 102 | Elijah Wood      |
| 103 | Robert Downey Jr |
+-----+------------------+

Here is my second table movie

+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Harry Potter 1      |
|  2 | Harry Potter 2      |
|  3 | Harry Potter 3      |
|  4 | Lord of the Rings 1 |
|  5 | Lord of the Rings 2 |
|  6 | Lord of the Rings 3 |
|  7 | Iron Man 1          |
|  8 | Iron Man 2          |
+----+---------------------+

Here is my last table list

+---------+----------+ 
| user_id | movie_id | 
+---------+----------+ 
|     101 |        1 | 
|     101 |        2 | 
|     101 |        3 | 
|     102 |        4 | 
|     102 |        5 | 
|     102 |        6 | 
|     103 |        7 | 
|     103 |        8 | 
+---------+----------+ 

Now I want to display name and movie side by side something like this:

+------------------+--------------------+
| Daniel Radcliffe | Harry Potter 1     |
| Daniel Radcliffe | Harry Potter 2     |
| Daniel Radcliffe | Harry Potter 3     |
| Elijah Wood      | Lord of the Rings 1|
| Elijah Wood      | Lord of the Rings 2|
| Elijah Wood      | Lord of the Rings 3|
| Robert Downey Jr | Iron Man 1         |
| Robert Downey Jr | Iron Man 2         |
+------------------+--------------------+

The list table has foreign keys for user_id and movie_id which reference user.id and movie.id respectively. So how can I use JOINs to display the above data.

  • What have you tried? Edit your query into the question, and the problem you're having with it. If you don't have a query and just need info on JOIN, trying Googling MySQL "inner join". – RDFozz Jun 5 '18 at 16:56
  • @RDFozz This question has already been answered and resolved. Thanks. – MikeJoe Jun 5 '18 at 17:08
  • Your question came up in a review task - I responded to it in that spirit, even though someone had answered. Consider it useful info for the future. – RDFozz Jun 5 '18 at 17:46
2
SELECT user.name, movie.name
FROM user, movie, list
WHERE list.user_id  = user.id
  AND list.movie_id = movie.id
ORDER BY 1,2;

Or, if you need to see the word JOIN in the query text

SELECT user.name, movie.name
FROM user
JOIN list  ON list.user_id  = user.id
JOIN movie ON list.movie_id = movie.id
ORDER BY 1,2;
  • It's better to refer the columns by explicit names instead of positional indexes. – Kondybas Jun 5 '18 at 15:59
  • In those case order by is optional at all... it was added only for the output to be fully equal to shown one... – Akina Jun 5 '18 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.