CODEWITHSUNDEEP
mysqlsqlselectfilter
user157629
user157629

Reputation: 634

How to exclude some data on a SQL query depending on it's different values?

I have the following 2 tables:

create table Director (

    id_director int,
    name varchar(255),
    primary key (id_director)
);

create table Movies (

    id_movie int,
    id_director int,
    title varchar(255),
    year int,
    primary key (id_movie),
    foreign key (id_director) references Director (id_director)
);

Some data has been already inserted on this tables. Imagine the data is:

Table Director

id_director       name
-------------------------
   1               Charles
   2               Jack
   3               Katia
   4               David

Table Movies

id_movie      id_director     title        year
------------------------------------------------
   1              2            title1      1999
   2              1            title2      2011
   3              1            title3      2019
   4              3            title4      2019
   5              4            title5      1968
   6              3            title6      2020
   7              2            title7      1977
   8              4            title8      2004
   9              1            title9      2020

I want to execute a SELECT clause in order to filter the directors that it's first movie its from 2019. Note from the example that only the director with the id_director = 3 meets the requirement as has made 2 films, one on 2019 and the other one on 2020.

How can I do this?

The output I would like to get is:

id_director          name
------------------------------
    3                Katia

I have tried this, but it's obviously wrong because some directors that made films before 2019, have also made films after 2019.

select director.id_director, director.name from director, movies
where director.id_director = movie.id_director and year = 2019
group by (name);

I'm using MySQL and I'm supposing I can do this with an IN / NOT IN subquery (but how)?

Upvotes: 1

Views: 55

Answers (4)

Fabiano Pontes
Fabiano Pontes

Reputation: 11

try this query

select d.id_director,d.name from director d
inner join movies m on
d.id_director=m.id_director
group by d.name,d.id_director
having min(m.year)>=2019;

Best regards,

Fabiano Pontes

Upvotes: 1

Luuk
Luuk

Reputation: 14899

dbfiddle

SELECT id_director, name
FROM Director
WHERE 2019 = (SELECT MIN(year) 
              FROM Movies 
              WHERE Movies.id_director=Director.id_director);

Upvotes: 0

alex stacey
alex stacey

Reputation: 447

If I understand right you want to look at the min(year) for that director. So the SQL would be something like:

select * from movies m
inner join directors d on m.id_director = d.id_director
where min(m.year) = 2019
group by d.id_director;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use correlated subquery : 

select d.*
from director d  
where 2019 = (select min(m.year) from movies m where m.id_director = d.id_director);

Upvotes: 0

Related Questions