Table Relationship Doubt

Question

Table Relationship Doubt

Navigation

#1 by (0 votes)
#2 by (0 votes)

-2

Good morning I would like a help from how do I get two information in one table from another. I have a table of notes where I have the agent_id and ID_endereco, I want to get the agent name and the customer name in the table whose people field is personas.cgccpf.

To have the cgccpf of the agent I need to relate the agent_id of the note to the users table by the agent_id To have the client's cgccpf I have to relate the id_endereco to the address table by id_endereco. Bye all right inner join agents on agent.id_agent = notes.id_agent inner join address on endereco.id_endereco = notes.id_endereco

But how do I get the address.cgccpf and the agent.cgccpf in both and show the names of each one in the people table?

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

sql-server

asked by anonymous 02.12.2015 / 11:31

2 answers

I can not send values to WebService .NET Is it possible to return a value using RemoteCommand?

score 0 · Answer 1

0

To have the cgccpf of the client I have to relate the id_endereco with the address table by id_endereco.

Why does the client's cgcpfg stay in the address table? should not be on the user table?

02.12.2015 / 12:31

score 0 · Answer 2

I tried to understand your structure from your comments, so I believe the following select will solve 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's just a few supplementary suggestions for the AP.

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

Another point, I do not know how your structure is, but it is interresting you to create an FK for these relationships, and an index 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)