how do I find the number of movies made by a specific actor in each year from 2 sql tables, taking startyear attribute from one table and actor name from another table?

CodePudding user response：

did you mean like this?

create movie table

create table movies (id bigint, title varchar(20), actor_id bigint, release_year int )

insert movie example data

insert into movies (id,title,actor_id,release_year) values (1,'movie 1',1,2022) insert into movies (id,title,actor_id,release_year) values (2,'movie 2',2,2029) insert into movies (id,title,actor_id,release_year) values (3,'movie 4',4,2030) insert into movies (id,title,actor_id,release_year) values (4,'movie 3',4,2012) insert into movies (id,title,actor_id,release_year) values (5,'movie 6',3,2022)

create actor table

create table actors(id bigint,actor_name varchar(20) )

insert actor example data

insert into actors (id,actor_name) values (1,'actor 1') insert into actors (id,actor_name) values (2,'actor 2') insert into actors (id,actor_name) values (3,'actor 3') insert into actors (id,actor_name) values (4,'actor 4')

query

select release_year, count(1) as [number of movies] from movies join actors on movies.actor_id = actors.id where actor_name = 'actor 4' group by release_year order by release_year asc

the result will be like this

image

CodePudding user response：

You will want to use a JOIN statement, as explained here: https://en.wikipedia.org/wiki/Join_(SQL)

CodePudding user response：

You Need to use the Joins to select Attributes from more than one table. Need to choose which Join need to use based on the requirement.

Inner Join

Right Join

Left Join

Full Join