SQLite Forum

Select and count from 2 tables
Login

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.