0
I need my query to return who is the direct partner of the company "Father" and the company "daughter". I already got him to return the daughter company and the direct partner, but I need him to return me if the daughter company has a partner. Could someone help me?
CREATE TABLE [dbo].[PessoaJuridica] (
[Id] INT IDENTITY (2, 1) NOT NULL,
[Nome] VARCHAR (50) NULL,
[CNPJ] VARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC));
CREATE TABLE [dbo].[PessoaFisica] (
[Id] INT IDENTITY (1, 3) NOT NULL,
[nome] VARCHAR (50) NULL,
[CPF] VARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC));
CREATE TABLE [dbo].[Filhoes] (
[Id] INT IDENTITY (2, 1) NOT NULL,
[idFisicaFilho] INT NULL,
[idJuridicaPai] INT NULL,
[idJuridicaFilho] INT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_JuridicaFilho] FOREIGN KEY ([idJuridicaFilho]) REFERENCES [dbo].[PessoaJuridica] ([Id]),
CONSTRAINT [FK_JuridicaPai] FOREIGN KEY ([idJuridicaPai]) REFERENCES [dbo].[PessoaJuridica] ([Id]),
CONSTRAINT [FK_PessoaFisica] FOREIGN KEY ([idFisicaFilho]) REFERENCES [dbo].[PessoaFisica] ([Id]));
The query:
SELECT * FROM Filhoes
Returns:
Id |idFisicaFilho |idJuridicaPai |idJuridicaFilho
1 | NULL | 1003 | 1004
2 | 3004 | 1003 | NULL
3 | 3007 | 1004 | NULL
And the query:
SELECT
pjf.Nome,
pjf.CNPJ,
pf.nome,
pf.CPF
FROM Filhoes f
LEFT JOIN PessoaJuridica pjp on pjp.Id = f.idJuridicaPai
LEFT JOIN PessoaJuridica pjf on pjf.Id = f.idJuridicaFilho
LEFT JOIN PessoaFisica pf on pf.Id = f.idFisicaFilho
WHERE f.idJuridicaPai = '1003'
Returns:
Subordinado | CNPJ |Socio |CPF
teste2 | 29.788.592/0001-30 | NULL | NULL
NULL | NULL | Joaquim | 664.341.440-75
How should I return:
Subordinado | CNPJ |Socio |CPF
teste2 | 29.788.592/0001-30 | José | 494.737.180-39
NULL | NULL | Joaquim | 664.341.440-75
Speaks Arthur. Edits your question with the structure of the tables involved.
– vinibrsl
Important [Edit] better describing the structure of each of the tables, and an example of how the result should be (with the respective data that gives rise to the intended output), something like a [MCVE]. Whereas you’re just using
pfj
andpf
in select fields, only two tables will be returned. If you can still complement with a SQL Fiddle or similar (as a complement, the question should not depend on external links) would help the community to propose solutions.– Bacco
@Arthuralberto (1) A company may have a single partner or may have more than one partner? (2) A company can have a single daughter company or it can have several daughter companies? (3) Same person can be a partner in more than one company?
– José Diz
Arthur, stay tuned for setting column sizes of variable size. For example, for the CPF use
char(11)
, for CNPJ usechar(14)
. This optimizes memory management of SQL Server. See Article https://portosql.wordpress.com/2018/09/14/howto define/– José Diz
@Josédiz (1) more than one partner, (2) several daughter companies, (3) Yes.
char
this is just a template of the main project. I need to create this select to generate a report.– Arthur Alberto