Join in 3 tables

Asked

Viewed 82 times

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.

  • 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 and pf 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.

  • @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?

  • Arthur, stay tuned for setting column sizes of variable size. For example, for the CPF use char(11), for CNPJ use char(14). This optimizes memory management of SQL Server. See Article https://portosql.wordpress.com/2018/09/14/howto define/

  • @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.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.