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

Question

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)?

Tag the DBMS that you are using.

Yogesh Sharma
– Yogesh Sharma

2020-02-20 15:05:31 +00:00
Commented Feb 20, 2020 at 15:05 — Yogesh Sharma
– Yogesh Sharma, Commented Feb 20, 2020 at 15:05

alex stacey · Accepted Answer · 2020-02-20 15:07:40Z

1

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;

answered Feb 20, 2020 at 15:07

alex stacey

4473 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user157629 Over a year ago

This worked, thanks. Is there any difference on doing this instead of doing a subquery as the other answers?

alex stacey Over a year ago

It's probably a bit more efficient than a subquery.

Fabiano Pontes · Accepted Answer · 2020-02-21 13:06:08Z

1

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

answered Feb 21, 2020 at 13:06

Fabiano Pontes

111 gold badge1 silver badge3 bronze badges

1 Comment

luca.vercelli Over a year ago

ok for the query, however, can you explain some more? what is the "key point" of this query?

Yogesh Sharma · Accepted Answer · 2020-02-20 15:04:52Z

0

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);

answered Feb 20, 2020 at 15:04

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Comments

Luuk · Accepted Answer · 2020-02-20 15:08:23Z

0

dbfiddle

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

answered Feb 20, 2020 at 15:08

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related