Select and count from 2 tables
(1) By Danny (dannydebont) on 2021-08-06 09:11:53 [link] [source]
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] [source]
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 [link] [source]
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] [source]
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 [source]
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] [source]
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.