MySQL - one table, multiple rows with repeated id

Question

So I have a "genres" table that has repeated genres and an id, and I want to formulate a query so that I return all the rows that have both genres for a specific id.

For example,

+---------+-------+
| genre   | movie |
+---------+-------+
| Musical |   558 |
| Musical |   562 |
| Musical |   597 |
| Musical |   651 |
| Musical |   656 |
| Musical |   791 |
| Musical |   810 |
| Musical |   845 |
| Musical |   859 |
| Musical |   919 |
| Musical |   949 |
| Musical |   971 |
+---------+-------+
12 rows in set (0.00 sec)

and

+--------+-------+
| genre  | movie |
+--------+-------+
| Comedy |   642 |
| Comedy |   643 |
| Comedy |   644 |
| Comedy |   651 |
| Comedy |   654 |
| Comedy |   658 |
+--------+-------+
6 rows in set (0.00 sec)

I would want it to return "movie 651" because it is both in comedy and musical (assuming a query of both 'comedy' and 'musical').

We seem to be getting this question every day. Try http://stackoverflow.com/questions/7407001/mysql-select-from-associative-table/7407063#7407063 ? — Konerak, Sep 14 '11 at 06:18

score 4 · Answer 1 · edited Sep 14 '11 at 06:35

4

use

SELECT DISTINCT A.MOVIE FROM GENRES A, GENRES B WHERE A.MOVIE = B.MOVIE AND A.GENRE = 'Comedy' AND B.GENRE = 'Musical';

EDIT - as per comment:

SELECT DISTINCT A.MOVIE 
FROM
    GENRES A 
  INNER JOIN
    GENRES B
        ON A.MOVIE = B.MOVIE
WHERE A.GENRE = 'Comedy'
  AND B.GENRE = 'Musical';

edited Sep 14 '11 at 06:35

ypercubeᵀᴹ

113,259
19
174
235

answered Sep 14 '11 at 06:10

Yahia

69,653
9
115
144

with slight tweaking SELECT a.movie FROM genres a, genres b WHERE a.movie = b.movie and a.genre = 'Comedy' AND b.genre = 'Musical'; – JDelonge Sep 14 '11 at 06:17
why the downvote ? if there is something to improve: an explanation would be nice... – Yahia Sep 14 '11 at 06:23
I did not downvote. But you should start using the `JOIN` syntax instead of the (implicit) join with `WHERE`. – ypercubeᵀᴹ Sep 14 '11 at 06:32
1

Down vote is because of the old join syntax, if you corrected to 92 syntax, it will be taken off – ajreal Sep 14 '11 at 06:32
@ajreal: why you deleted your answer? It's the alternative way of doing this kind of queries. – ypercubeᵀᴹ Sep 14 '11 at 06:38
@ajreal just change `COUNT(*)` to `COUNT(DISTINCT MOVIE)` to account for what I commented on... – Yahia Sep 14 '11 at 06:42

v42 · Answer 2 · 2011-09-14T07:09:39.750

1

 SELECT DISTINCT
    movie
 FROM
    genres
 WHERE
    movie in (SELECT movie from genres where genre = "Musical")
    AND movie in (SELECT movie from genres where genre = "Comedy")

edited Sep 14 '11 at 07:09

answered Sep 14 '11 at 06:11

v42

1,415
14
23

score 0 · Answer 3 · answered Sep 14 '11 at 06:08

0

This will give you the list of ids in both

select movie from table a join table b on a.movie = b.movie

answered Sep 14 '11 at 06:08

Jan S

1,831
15
21

MySQL - one table, multiple rows with repeated id

3 Answers3