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

Question

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

Navigation

#1 by (0 votes)

0

I have the SQL command that does not 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;

In the GAT_requisicao table I have 2 columns:

sectorReq_GATrequisition
sectorExec_GATrequisition

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

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

mysql sql

asked by anonymous 30.08.2017 / 16:10

1 answer

Using "select" in sql (oracle), failed to fetch Problem implementing JSF Converter with p: selectOneMenu

score 0 · Accepted Answer

You want to make join for the same table from different fks, so you can use inner join instead of where , and properly bind the keys, like this:

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;