How to join data from one table to another that have the same identifier?

Question

How to join data from one table to another that have the same identifier?

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

create table usuarioEmpleado(
id_empleado int not null primary key,
id_tipo int not null references tipoUsuario(id_tipo),
nombres varchar(150) not null,
apellidos varchar(150) not null,
telefono int not null,
correo_electronico varchar(70) not null,
contraseña varchar(80) not null,
direccion varchar(120) not null,
id_estado int not null references estadoEmpleado(id_estado)
);
insert into usuarioEmpleado values(1,1,'Leonardo','Garcia',77814435,'[email protected]','leo123','san salvador',1)


create table documento(
id_documento int not null primary key,
nombre varchar(80)
);
insert into documento values(1,'DUI'),(2,'AFP')

create table documentoEmpleado(
id_documentoE int not null primary key,
id_documento int not null references documento(id_documento),
id_empleado int not null references usuarioEmpleado(id_empleado),
descripcion int not null
);

If I make the following query:

select E.nombres, E.apellidos,D.nombre, DE.descripcion
from documentoEmpleado DE, usuarioEmpleado E, documento D
where D.id_documento=DE.id_documento AND E.id_empleado=DE.id_empleado

Show me:

1.Leonardo  Garcia  DUI 20170199
2.Leonardo  Garcia  AFP 20140680

I want you to show me:

1.Leonardo Garcia DUI 20170199 AFP 20140680

sql-server

asked by Steven Diaz 23.05.2018 в 19:51

source

2 answers

problem "same name are not supported" c # [closed] I can not upload my app to the playstore console

score 0 · Answer 1

Have you tried using INNER JOIN ?

Example.

select 
E.nombres
,E.apellidos
,D.nombre
,DE.descripcion
from 
documentoEmpleado DE INNER JOIN usuarioEmpleado E ON
E.id_empleado = DE.id_empleado
INNER JOIN documento D ON
D.id_documento = DE.id_documento

Greetings.

score 0 · Answer 2

You can use xml path to achieve what you're looking for.

In this case, a code example speaks more than a thousand words.

with
UsuarioEmpleado as (
          select 1 id_Empleado, 'Leonardo' Nombres, 'Garcia' Apellidos
union all select 2, 'Juan', 'Perez'
)
,
Documento as (
          select 1 id_Documento, 'DUI' nombre
union all select 2, 'AFP'
)
,
DocumentoEmpleado as (
          select 1 id_DocumentoE, 1 id_Documento, 1 id_Empleado, '20170199' Descripcion 
union all select 2, 2, 1, '20140680'
union all select 3, 1, 2, '12345'
union all select 4, 2, 2, '67890'
)
,
ListaDocumentos as (
select   DE.id_empleado
       , coalesce(E.nombres, '') + ' ' + coalesce(E.apellidos, '') NombreCompleto
       , coalesce(D.nombre, '') + ' ' + coalesce(DE.descripcion, '') Documento
  from documentoEmpleado DE
       inner join usuarioEmpleado E on E.id_empleado = DE.id_empleado
       inner join documento D on D.id_documento = DE.id_documento
)
select distinct 
         a.NombreCompleto
       , stuff((select distinct ', ' + b.Documento
                  from ListaDocumentos b
                 where b.id_Empleado = a.id_Empleado
                 for xml path(''), type
               ).value('.', 'NVARCHAR(MAX)')
             , 1, 2, '') Documentos
  from ListaDocumentos a

The result it throws is as follows:

NombreCompleto    Documentos
================= ===============================
Juan Perez        AFP 67890, DUI 12345
Leonardo Garcia   AFP 20140680, DUI 20170199