Doubt of relationship of tables

Question

Good morning I would like a help of how do I get two information on one table from another. example I have a table of notes where I have the Id_agent and the Id_address, I want to get the agent name and the client name in the table people whose key field is people.cgccpf.

To have the agent cgccpf need to relate the note’s id_agent to the table users by the id_agent To have the client’s cgccpf I have to relate the id_addressee with the table addressed by the id_addressee. So far so good Inner Join agents on agent.id_agent = notes.id_agent Inner Join address on address.id_address = notes.id_address

However as I take the address.cgccpf and the agent.cgccpf in both and I show the names of each one that is in the table people ?

CREATE TABLE Notas (
    id_nfcapa integer NOT NULL,
    cgccpf decimal(14,0) NOT NULL,
    tppessoa smallint NULL,
    nronota integer NOT NULL,
    serienf char(4) NOT NULL, 
    nro_endere decimal(17,0) NOT NULL, -- FK Endereço
    id_agente integer NOT NULL -- FK Agente
);

CREATE TABLE Agentes (
    id_agente integer NOT NULL, -- PK Agente
    id_setor integer NOT NULL, 
    tppessoa smallint NOT NULL, 
    cgccpf decimal(14,0) NOT NULL, -- FK Pessoa
    cargo smallint NOT NULL
);  

CREATE TABLE Endereco (
    nro_endere decimal(17,0) NULL, -- PK Endereço
    cgccpf decimal(14,0) NOT NULL, -- FK Pessoa
    tppessoa smallint NOT NULL,  
    seqendereco smallint NOT NULL,  
    tipoendereco char(1) NULL, 
    endereco char(30) NULL, 
    complemento char(10) NULL
);  

CREATE TABLE Pessoas (
    cgccpf decimal(14,0) NOT NULL, -- PK Pessoa
    tppessoa smallint NOT NULL,  
    nomepessoa char(40) NULL,  
    nomeguerra char(20) NULL,  
    dtfundacao date NULL,  
    rg char(14) NULL, 
);  

Thank you

Ronie

Answer 1

To have the client’s cgccpf I have to relate the id_addressee with the table addressed by the id_addressee.

Why is the client’s cgcpfg in the address table ?? should not be in the users' table?

Answer 2

I tried to understand its structure from your comments, so I believe the following select solves your problem:

SELECT 
    Notas.id_nfcapa,
    Notas.cgccpf,
    Notas.tppessoa,
    Notas.nronota,
    Notas.serienf,
    PesCliente.nomepessoa,
    PesAgente.nomepessoa
FROM Notas
JOIN Agentes ON Notas.id_agente = Agentes.id_agente
JOIN Endereco ON Notas.nro_endere = Endereco.nro_endere
JOIN Pessoas as PesAgente ON Agentes.cgccpf = PesAgente.cgccpf
JOIN Pessoas as PesCliente ON Endereco.cgccpf = PesCliente.cgccpf

BONUS - Suggestions

The text below is not part of the answer, it is just some complementary suggestions for the AP.

In any case, I would advise you to read the following Choosing a Primary Key: Natural or Surrogate?, I especially don’t like having chaves naturais as primaries... In 90% of cases, have a chave substituta (no value to the business) that is IDENTITY with a Clustred Index associated as PK is the best option.

Another point, I don’t know what your structure looks like, but it’s interesting that you create an FK for these relationships, and an input for each relationship:

ALTER TABLE Notas WITH CHECK ADD CONSTRAINT [FK_Notas_Endereco] 
FOREIGN KEY(nro_endere) REFERENCES Endereco (nro_endere)

ALTER TABLE Notas WITH CHECK ADD CONSTRAINT [FK_Notas_Agentes] 
FOREIGN KEY(id_agente) REFERENCES Agentes (id_agente)

ALTER TABLE Agentes WITH CHECK ADD CONSTRAINT [FK_Agentes_Pessoas] 
FOREIGN KEY(cgccpf) REFERENCES Pessoas (cgccpf)

ALTER TABLE Endereco WITH CHECK ADD CONSTRAINT [FK_Endereco_Pessoas] 
FOREIGN KEY(cgccpf) REFERENCES Pessoas (cgccpf)

CREATE NONCLUSTERED INDEX IX_id_agente ON Notas (id_agente ASC)
CREATE NONCLUSTERED INDEX IX_nro_endere ON Notas (nro_endere ASC)
CREATE NONCLUSTERED INDEX IX_cgccpf ON Agentes (cgccpf ASC)
CREATE NONCLUSTERED INDEX IX_cgccpf ON Endereco (cgccpf ASC)