How to make a Join of 2 tables and where 2 fields of table A refer to the same field of table B?

Question

I have the command SQL that doesn’t work:

SELECT
    GAT_requisicao.id_GATrequisicao as 'ID Requisição',
    GAP_atendimento.id_GAPatendimento as 'ID Atendimento',
    GR_paciente.nome_GRpaciente as 'Nome Paciente',
    GR_setor.nome_GRsetor as 'Setor Requisitante',
    GR_setor.nome_GRsetor as 'Setor Executante'
FROM
    GAT_requisicao, GAP_atendimento, GR_paciente, GR_setor
WHERE
    GAT_requisicao.GAPatendimento_id = GAP_atendimento.id_GAPatendimento
AND GAP_atendimento.paciente_GAPatendimento = GR_paciente.nrcarteira_GRpaciente
AND GAT_requisicao.setorReq_GATrequisicao = GR_setor.id_GRsetor
AND GAT_requisicao.setorExec_GATrequisicao = GR_setor.id_GRsetor;

On the table GAT_requisicao I have 2 columns:

• setorReq_GATrequisicao
• setorExec_GATrequisicao

One is saved the requesting sector and the other the executing sector and these columns are filled with FK table GR_setor, but what happens:

If I ask on select to see the setorReq and the setorExec and connect the 2 tables, does not return me anything, I can only make 1 call at a time, or call setorExec the table GR_setor or call setorReq the table GR_setor, the 2 links at the same return the return comes null.

Answer 1

You want to do the join for the same table from different fks, then you can use the inner join in place of where, and properly turn on the keys, so:

SELECT
    GAT_requisicao.id_GATrequisicao as 'ID Requisição',
    GAP_atendimento.id_GAPatendimento as 'ID Atendimento',
    GR_paciente.nome_GRpaciente as 'Nome Paciente',
    GR_setor.nome_GRsetor as 'Setor Requisitante',
    GR_setor.nome_GRsetor as 'Setor Executante'
FROM
    GAT_requisicao
INNER JOIN GAP_atendimento ON GAT_requisicao.GAPatendimento_id = GAP_atendimento.id_GAPatendimento
INNER JOIN GR_paciente ON GAP_atendimento.paciente_GAPatendimento = GR_paciente.nrcarteira_GRpaciente
INNER JOIN GR_setor AS GR_setor_req ON GAT_requisicao.setorReq_GATrequisicao = GR_setor_req.id_GRsetor
INNER JOIN GR_setor AS GR_setor_exec ON GAT_requisicao.setorExec_GATrequisicao = GR_setor_exec.id_GRsetor;