For the actors having more than one release in a year print the movies they have worked on-CodePudding

i have a movies table (id(pk), movie_name, year_of_release, language), actors table(id(pk), actor_name, gender, dob), actors_in_movies table (id(pk), movie_id, actor_id)

the question is to use sql subquery in POSTGRES For the actors having more than one release in a year and print as output the movies they have worked on.

actors:

    **id  actor_name          dob     gender**
    1   "Shah Rukh Khan"    1965    "M"
    2   "Deepika Padukon"   1984    "F"
    3   "Johnny Depp"   1975    "M"

movies:

    **id   movie_name  year_of_release  language**
    1   "Sholay"    1993    "Hindi"
    2   "Taare Zameen Par"  2008    "Hindi"
    3   "Avengers End Game" 2010    "English"
    4   "Squid Game"    2008    "Korean"
    5   "Boys Over Flower"  2010    "Korean"
    6   "The Family Man"    2010    "Hindi"
    7   "Ask Laftan Anlamaz"    2016    "Turkish"
    8   "Dangal"    2010    "Hindi"

actors_in_movies

    **id movie_id actor_id**
    1        1       1
    2        3       1
    3        2       2
    4        3       3
    5        4       1
    6        6      2
    7       7      1
    8       5       2
    9       8       3

expected output:

    **movie_name   year_of_release   actor_name**
    avengers end game   2010       johny depp
    dangal              2010        johny depp
    the family man        2010      deepika padukon
    boys over flower      2010      deepika padukon

CodePudding user response：

with cte as(
select c.id,year(year_of_release),count(b.*)
from actors c 
left join actors_in_movies b on c.id=b.actor_id 
left join movies a on a.id=b.movie_id
group by c.id,year_of_release
having count(b.*)>1)

select c.*
from cte a inner join actors_in_movies b on a.id=b.actor_id 
inner join movies c on b.movie_id=c.id

CodePudding user response：

Year function is based on machine your working on here used MSSQL function year if your using postgres just replace year(year_of_release) with to_char(year_of_release,'YYYY-MM-DD')