Find the name and number of doctors who have appointments with all patients

Question

No postgres, as Fetch the name and CPF of doctors who have appointments with all patients?

Keys: Patient: id_patient Medico: id_medico consultation: id_patient, id_doctor

Answer 1

You need to select all the medical ids present in the consultation table, and make a Join to search the respective doctor’s data, see the code below:

SELECT distinct 
    c.id_medico,
    m.nome as nome_medico,
    m.cpf as cpf_medico
FROM consultas c
LEFT OUTER JOIN medicos m on m.id = c.id_medico

When posting a question, post also the structure that has your database and the code you are using.

Answer 2

Assuming your table structure is anything like this:

CREATE TABLE  paciente
(
    id_paciente INTEGER PRIMARY KEY,
    nome TEXT
);

CREATE TABLE  medico
(
    id_medico INTEGER PRIMARY KEY,
    nome text,
    cpf text
);

CREATE TABLE consulta
(
    id_paciente INTEGER,
    id_medico INTEGER,
    FOREIGN KEY ( id_paciente ) REFERENCES paciente ( id_paciente ),
    FOREIGN KEY ( id_medico ) REFERENCES medico ( id_medico )
);

With the following test load:

INSERT INTO paciente ( id_paciente, nome ) VALUES ( 1, 'Joao' );
INSERT INTO paciente ( id_paciente, nome ) VALUES ( 2, 'Maria' );
INSERT INTO paciente ( id_paciente, nome ) VALUES ( 3, 'Jose' );
INSERT INTO paciente ( id_paciente, nome ) VALUES ( 4, 'Ana' );
INSERT INTO paciente ( id_paciente, nome ) VALUES ( 5, 'Luiz' );

INSERT INTO medico ( id_medico, nome, cpf ) VALUES ( 1, 'Oswaldo Cruz', '123.123.123-00' );
INSERT INTO medico ( id_medico, nome, cpf ) VALUES ( 2, 'Carlos Chagas', '900.900.900-99' );
INSERT INTO medico ( id_medico, nome, cpf ) VALUES ( 3, 'Vital Brazil', '111.222.333-99' );
INSERT INTO medico ( id_medico, nome, cpf ) VALUES ( 4, 'Ze Ninguem', '000.000.000.-00' );

INSERT INTO consulta ( id_paciente, id_medico ) VALUES ( 1, 1 );
INSERT INTO consulta ( id_paciente, id_medico ) VALUES ( 2, 1 );
INSERT INTO consulta ( id_paciente, id_medico ) VALUES ( 3, 1 );
INSERT INTO consulta ( id_paciente, id_medico ) VALUES ( 4, 2 );
INSERT INTO consulta ( id_paciente, id_medico ) VALUES ( 5, 2 );
INSERT INTO consulta ( id_paciente, id_medico ) VALUES ( 2, 3 );
INSERT INTO consulta ( id_paciente, id_medico ) VALUES ( 4, 3 );

The consultation would look like this:

SELECT
    m.cpf AS cpf_medico,
    m.nome AS nome_medico,
    p.nome AS nome_paciente
FROM
    consulta c
JOIN
    medico m ON ( c.id_medico = m.id_medico )
JOIN
    paciente p ON ( c.id_paciente = p.id_paciente )
ORDER BY 
    m.cpf;

Exit:

|     cpf_medico |   nome_medico | nome_paciente |
|----------------|---------------|---------------|
| 111.222.333-99 |  Vital Brazil |         Maria |
| 111.222.333-99 |  Vital Brazil |           Ana |
| 123.123.123-00 |  Oswaldo Cruz |          Jose |
| 123.123.123-00 |  Oswaldo Cruz |          Joao |
| 123.123.123-00 |  Oswaldo Cruz |         Maria |
| 900.900.900-99 | Carlos Chagas |           Ana |
| 900.900.900-99 | Carlos Chagas |          Luiz |

Sqlfiddle