-2
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
id_agent does not relate to people.id?
– Miguel Batista
You want to get the cgccpf from the address and the agent, and both are different, correct?
– Rafael Lincoln
post the structure of its tables, if but before a look here: http://answall.com/questions/99874/como-fazer-select-em-3-tabelas/99877#99877
– David Schrammel
post a print of the structure of your tables
– Miguel Batista
Notes: Column name Type Nulls id_nfcapa integer no cgccpf decimal(14,0) no tppessoa smallint yes nronota integer no serienf char(4) no nro_decimal address(17,0) no <--- id_agent integer in <---
– Ronie
Agents Column name Type Nulls id_agent integer no <--- id_sector integer no tppessoa smallint no cgccpf decimal(14,0) no cargo smallint no
– Ronie
Address Column name Type Nulls nro_decimal address(17,0) yes <---- cgccpf decimal(14,0) no tppessoa smallint no seqendereco smallint no typoendereco char(1) yes char(30) yes complement char(10) yes
– Ronie
Persons: Column name Type Nulls cgccpf decimal(14,0) no <-- tppessoa smallint no person name char(40) yes warname char(20) yes dtfoundation date yes rg char(14) yes
– Ronie
Due to the character limit these are parts of the structures with the attributes that matter.
– Ronie
@Ronie, I moved your comments to your question just as I tried to make them more readable, so please check out my edition.
– Tobias Mesquita