I need my query to return who is the direct partner of the company "Father" and the company "daughter". I got him back to the daughter company and the direct partner, but I need him to get me back if the daughter company has a partner. can anybody 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]));
A query:
SELECT * FROM Filhoes
Return:
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'
Return:
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