Query with pivot table - SQL Server 2014

Question

Query with pivot table - SQL Server 2014

Navigation

#1 by (1 votes)

0

I have 3 tables:

Persona(IdPersona(PK), NombreYApellido, IdTelefono);  
Telefono(IdTelefono(PK), Numero, IdTipoTelefono);   
TipoTelefono(IdTipoTelefono(PK), Descripcion);

To relate them I created the table

PersonasTipoTelefono(IdPersonaTipoTelefono(PK), IdPersona, IdTelefono, IdTipoTelefono)

I need to bring all the phones that correspond to each person and I made the following query:

SELECT  P.[Nombre y Apellido], T.NumeroTelefono, TT.Descripcion, PTT.*
   FROM    PersonaTipoTelefono PTT INNER JOIN
           Personas P ON PTT.IdPersona = P.IdPersona INNER JOIN
           Telefono T ON PTT.IdTelefono = T.IdTelefono AND 
           P.IdTelefono = T.IdTelefono INNER JOIN
           TipoTelefono TT ON PTT.IdTipoTelefono = TT.IdTipoTelefono AND 
            T.IdTipoTelefono = TT.IdTipoTelefono

The problem is that it only returns a phone number for each person when there are people who have assigned 2 or 3 phone numbers.

sql sql-server query

asked by Pablo Matias 07.09.2017 в 17:18

source

1 answer

Create image with what is in a tab control c # [closed] Operation of form when changing it from input to select [closed]

score 1 · Accepted Answer

Pablo, in the first place, the title PIVOT, is confounded by the PIVOT clause, it would be best to speak of a "ternary" or "ternary" relationship. Second, I think the ternary relationship should be:

PersonasTelefono(IdPersonaTelefono(PK), IdPersona, IdTelefono)

The type of telephone is an attribute of the telephone and not of the person, think of it like this: "a person can have several telephones and each of these is of a type", the part "one person can have several telephones" you solve by means of the entity PersonasTelefono , and the one of "each of these is of a type" you already have it solved in the own entity / table Telefono and TipoTelefono . Finally your query would be something like this:

SELECT     P.NombreYApellido, 
           T.NumeroTelefono, 
           TT.Descripcion,
           FROM   Persona P
           INNER JOIN PersonasTelefono PT
                 ON PT.IdPersona = P.IdPersona
           INNER JOIN Telefono T
                 ON T.IdTelefono= P.IdTelefono
           INNER JOIN TipoTelefono TT 
               ON TT.IdTipoTelefono= T.IdTipoTelefono

I commented to you, that as the query is written, it would only list people who have a telephone, depending on the need this could be a problem, anyway you would solve it by using LEFT JOIN . Do not forget to also incorporate indexes in PersonasTelefono for IdPersona and IdTelefono that will allow the engine to optimize the query better.