Select and count from 2 tables
(1) By Danny (dannydebont) on 2021-08-06 09:11:53 [link]
Good day, I am trying to find how many movies are both in the genre "Romance" and "Comedy" I have a table here (https://i.imgur.com/dPJpZTb.jpg) and I am trying to find the answer, but my statement seems to be wrong. (https://i.imgur.com/xvbKCJy.jpg)
(2) By Harald Hanche-Olsen (hanche) on 2021-08-06 10:34:14 in reply to 1 [link]
I didn't try too hard decoding your pictures, but I note that the expression ``` genre_name = 'Romance' AND genre_name = 'Comedy' ``` will *never* be true. Perhaps you wanted to use `OR` instead? Or simpler: ``` genre_name IN ('Romance','Comedy') ```
(3) By Rico Mariani (rmariani) on 2021-08-06 22:35:49 in reply to 1
``` create table genremap(movie_id integer, name text); insert into genremap values(1, 'Comedy'), (2, 'Romance'), (3, 'Romance'), (3, 'Comedy'), (4, 'Romance'), (4, 'Comedy'); select count(*) from genremap T1 where T1.name = 'Romance' and exists( select 1 from genremap T2 where T1.movie_id = T2.movie_id AND T2.name = 'Comedy' ); result: 2 ```
(4) By Keith Medcalf (kmedcalf) on 2021-08-06 22:48:12 in reply to 1 [link]
How about translate directly from the English requirements statement into SQL? ``` select movie_id from genremap, genres where genremap.genre_id == genres.genre_id and genre_name == 'Romance' INTERSECT select movie_id from genremap, genres where genremap.genre_id == genres.genre_id and genre_name == 'Comedy' ; ``` Also note that although pretty pictures may be pretty they are generally not useful. Textual description of the relevant tables is more environmentally friendly and wastes less energy and time.
(5) By Keith Medcalf (kmedcalf) on 2021-08-06 23:12:05 in reply to 4 [link]
Note that you can sprinkle semantic sugar on top if you like: ``` select movie_id from genremap join genres on genremap.genre_id == genres.genre_id where genre_name == 'Romance' INTERSECT select movie_id from genremap join genres on genremap.genre_id == genres.genre_id where genre_name == 'Comedy' ; ``` or perhaps even ``` select movie_id from genremap join genres using (genre_id) where genre_name == 'Romance' INTERSECT select movie_id from genremap join genres using (genre_id) where genre_name == 'Comedy' ; ``` or ``` select movie_id from genremap natural join genres where genre_name == 'Romance' INTERSECT select movie_id from genremap natural join genres where genre_name == 'Comedy' ; ```
(6) By Keith Medcalf (kmedcalf) on 2021-08-06 23:19:20 in reply to 4 [link]
Note that these return the list of movie_id. If you want the count, then simply count them: ``` select count(movie_id) from ( select movie_id from genremap, genres where genremap.genre_id == genres.genre_id and genre_name == 'Romance' INTERSECT select movie_id from genremap, genres where genremap.genre_id == genres.genre_id and genre_name == 'Comedy' ) ; ``` Sugary sprinkles can be used in the sub-select if you want.