SQL showing records if they only meet different conditions than usual

Question

I have a slightly different situation, tried different conditions but none brought the desired result.

My need is this:

I have a base containing authors of books and their respective works.

I need to create a query that brings only the authors who had the first book published from 1919.

Follow script for table creation:

CREATE TABLE AUTOR (
    ID INT,
    NOME VARCHAR(255),
    PRIMARY KEY (ID)
);

CREATE TABLE LIVROS (
    ID INT,
    ID_AUTOR INT,
    TITULO VARCHAR(255),
    ANO INT,
    PRIMARY KEY (ID),
    FOREIGN KEY (ID_AUTOR) REFERENCES AUTOR (ID)
);

Follows script for inserting the sampling data:

INSERT INTO AUTOR (ID, NOME) VALUES (1, 'MACHADO DE ASSIS');
INSERT INTO AUTOR (ID, NOME) VALUES (2, 'EUCLIDES DA CUNHA');
INSERT INTO AUTOR (ID, NOME) VALUES (3, 'CLARICE LISPECTOR');
INSERT INTO AUTOR (ID, NOME) VALUES (4, 'JOSÉ DE ALENCAR');
INSERT INTO AUTOR (ID, NOME) VALUES (5, 'MONTEIRO LOBATO');
INSERT INTO AUTOR (ID, NOME) VALUES (6, 'CECÍLIA MEIRELES');

INSERT INTO LIVROS (ID, ID_AUTOR, TITULO, ANO) VALUES (1, 5, 'O Saci-Pererê: resultado de um inquérito', 1918);
INSERT INTO LIVROS (ID, ID_AUTOR, TITULO, ANO) VALUES (2, 5, 'O Picapau Amarelo', 1939);
INSERT INTO LIVROS (ID, ID_AUTOR, TITULO, ANO) VALUES (3, 6, 'Espectros', 1919);
INSERT INTO LIVROS (ID, ID_AUTOR, TITULO, ANO) VALUES (4, 3, 'Perto do Coração Selvagem', 1944);

The correct query should bring the following result:

Result

| id(autor) |       nome(autor)      |
|------------------------------------|
|    6      |    CECÍLIA MEIRELES    |  
|    3      |    CLARICE LISPECTOR   |

Monteiro Lobato will not come in the consultation because it has a publication prior to the year 1919.

Answer 1

Using the IN clause:

SELECT * FROM AUTOR WHERE ID IN (SELECT ID_AUTOR FROM LIVROS GROUP BY ID_AUTOR HAVING MIN(ANO) >= 1919);

Answer 2

To understand more about JOINS, you can read INNER JOIN, LEFT JOIN and RIGHT JOIN clauses in SQL Server or JOINS in SQL. I recommend reading too: MIN(Transact-SQL) and SELECT - GROUP BY - Transact-SQL.

In your case, QUERY(Select) would look like this:

SELECT AUTOR.ID,AUTOR.NOME FROM AUTOR
INNER JOIN LIVROS ON
AUTOR.ID=LIVROS.ID_AUTOR
GROUP BY AUTOR.NOME,AUTOR.ID
HAVING MIN(LIVROS.ANO)>=1919;