Join in 3 tables

0

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
    
asked by anonymous 02.12.2018 / 19:01

0 answers