Doubt query in SQL Server - Information Association

Question

My friends, good night! I need to perform a query in a database using SQL Server and I got stuck in a problem so I ask, please help from you.

Here is an example summary of the problem:

• I have a table that contains the names of people (I will call TABLE 1);
• I have a table that groups certain people in TABLE 1 and establishes who is the main person and who are the secondary of this grouping (I will call TABLE 2);

TABLE 1 looks like this:

IdP   Nome
1    João
2    Maria
3    José

TABLE 2 looks like this:

IdG  IdP  Prcp
1    1    1
1    2    0
1    3    0

That is, in this example, the 3 people are grouped in the same group and John is the main and the others are secondary... I would like to build a query that returns:

Nome   NomePrincipal
João   João
Maria  João
José   João

I tried the following query, but unsuccessfully:

select T1.Nome as 'Nome', T1P.Nome as 'NomePrincipal' from TABELA2 T2
inner join TABELA1 T1 on T2.IdP = T1.IdP
left join TABELA1 T1P on T2.IdP = T1P.IdP AND T2.Prcp = 1

I’m getting the following result:

Nome   NomePrincipal
João   João
Maria  NULL
José   NULL

Thank you!

Answer 1

Try it this way: http://www.sqlfiddle.com/#! 9/f9a09c/3

The DDL below is optional,

CREATE TABLE Tabela1
(
  IdP INT NOT NULL PRIMARY KEY,
  Nome NVARCHAR(5) NOT NULL
)
GO

CREATE TABLE Tabela2
(
  IdG INT NOT NULL,
  IdP INT NOT NULL,
  Prcp INT NOT NULL,
  PRIMARY KEY PK_Tabela2 (IdG, IdP),
  FOREIGN KEY FK_Tabela2_Tabela1 (IdP) REFERENCES Tabela1 (IdP)
)
GO

The DML to play the contents of the database is optional, too,

INSERT INTO Tabela1 VALUES (1, 'João')
GO
INSERT INTO Tabela1 VALUES (2, 'Maria')
GO
INSERT INTO Tabela1 VALUES (3, 'José')
GO

INSERT INTO Tabela2 VALUES (1, 1, 1)
GO
INSERT INTO Tabela2 VALUES (1, 2, 0)
GO
INSERT INTO Tabela2 VALUES (1, 3, 0)
GO

Follows the DML:

SELECT t1.Nome, t1a.Nome Principal
FROM Tabela1 t1
JOIN Tabela2 t2 ON t2.IdP = t1.IdP
JOIN Tabela2 t2a ON t2a.IdG = t2.IdG AND t2a.Prcp = 1
JOIN Tabela1 t1a ON t1a.IdP = t2a.IdP
GO

Note: the fiddle is done in Mysql because apparently SQL Server 2017 available on the site was under maintenance.