Consulta con left join

arcr · #1 (**permalink**) 31/08/2012, 09:39

Hola que tal buen dia:

Bueno explico mi problema

Tengo la siguiente consulta

select z10des, z10ele from z10 where z10ser='A01' and z10lng='SPA'
and z10tab='DOCURACC' order by z10ele

que muestra los siguientes datos son el nombre del documento y la clave que lo identifica

Descripcion Codigo Documento
ACTA DE NACIMIENTO 010
CARTA DE NATURALIZACIÓN 020
IDENTIFICACIÓN OFICIAL 030
COMPROBANTE DE DOMICILIO 040
CURP 050
ACTA DE MATRIMONIO 060
CONTRATO DE AHORRO 070
ESCRITURA CONSTITUTIVA 110
CÉDULA DE IDENTIFIC. FISCAL 120
INSCRIPCIÓN REGISTRO PUBLICO 130
IDENT.OFICIAL REPRESENT.LEGAL 140
ESTADOS FINANCIEROS 150

Estos datos son los documentos requeridos de un socio de una caja popular

y tengo una segunda consulta

select z91rap, z91tdo from z91

que da como resultado

Cod. Socio Codigo documento
00000003 070
00000004 070
00000005 030
00000005 040
00000005 060
00000005 070
00000006 070
00000011 070

el resultado es la clave del socio y las claves de los documentos que ha entregado

lo que yo quiero como resultado seria algo como lo siguiente

Descripcion Codigo Doc. Cod. Socio Codigo Doc. Entrego
ACTA DE NACIMIENTO 010 00000003 Null No
CARTA DE NATURALIZACIÓN 020 00000003 Null No
IDENTIFICACIÓN OFICIAL 030 00000003 Null No
COMPROBANTE DE DOMICILIO 040 00000003 Null No
CURP 050 00000003 Null No
ACTA DE MATRIMONIO 060 00000003 Null No
CONTRATO DE AHORRO 070 00000003 070 Si
ESCRITURA CONSTITUTIVA 110 00000003 Null No
CÉDULA DE IDENTIFIC. FISCAL 120 00000003 Null No
INSCRIPCIÓN REGISTRO PUBLICO 130 00000003 Null No
IDENT.OFICIAL REPRESENT.LEGAL 140 00000003 Null No
ESTADOS FINANCIEROS 150 00000003 Null No

Quisiera obtener un resultado como el que se presenta anteriormente que por cada cliente me indique si ya fue entregado un documento o no y el codigo que
no se encuentre en la primer consulta que la ponga como null
Tengo entendido que eso puede hacerse con un left join
y lo que tengo hasta ahorita es la siguiente consulta

select z10des, z10ele, z91rap, z91tdo from z10 left join z91
on z10ele=z91tdo where z10ser='A01' and z10lng='SPA' and
z10tab='DOCURACC' order by z91rap

y obtengo los siguientes datos

Descripcion Cod. Documento Cod. Socio Cod. Documento
CONTRATO DE AHORRO 070 00000003 070
CONTRATO DE AHORRO 070 00000004 070
ACTA DE MATRIMONIO 060 00000005 060
COMPROBANTE DE DOMICILIO 040 00000005 040
CONTRATO DE AHORRO 070 00000005 070
IDENTIFICACIÓN OFICIAL 030 00000005 030
CONTRATO DE AHORRO 070 00000006 070

Es lo que he podido lograr hasta el momento no conozco mucho sobre la funcionalidad del left join y no tengo idea como sacar un resultado como el que necesito

porfavor si alguien pudiera ayudarme se lo agradeceria bastante

Muchas gracias y saludos

arcr · #2 (**permalink**) 31/08/2012, 09:40

Una disculpa por las alineaciones tan malas pero no aparecieron acomodadas como las puse aqui en el editor

Andres95 · #3 (**permalink**) 31/08/2012, 10:26

Tendrías que hacer primero un cross (producto cartesiano) para que te de la combinaciones de documentos vs clientes que estas consultando

y luego buscar esas combinaciones en la tabla de los doctos entregados por dichos clientes..

Algo mas o menos así..

Código:

SELECT c.z10des, 
       c.z10ele, 
       c.z91rap, 
       CASE WHEN z91tdo IS NULL THEN 'NO'  ELSE 'SI' END 
FROM   (
			SELECT z10des, 
				   z10ele, 
				   z91rap 
			FROM   z10 
				   CROSS JOIN (SELECT DISTINCT z91rap 
							   FROM   z91) s 
			WHERE  z10ser = 'A01' 
				   AND z10lng = 'SPA' 
				   AND z10tab = 'DOCURACC'
               ) C 
       LEFT JOIN z91 m 
              ON m.z91tdo = C.z10ele 
                 AND m.z91rap = c.z91rap 
ORDER  BY 3, 
          2

Saludos!

Libras · #4 (**permalink**) 31/08/2012, 10:50

Otra Opcion con un full outer join :)

Código SQL:

Ver originalCREATE TABLE #temp
(
descripcion VARCHAR(100),
cod_doc VARCHAR(20)
)
 
CREATE TABLE #temp2
(
cod_soc VARCHAR(20),
cod_doc VARCHAR(20)
)
 
 
INSERT INTO #temp VALUES ('ACTA DE NACIMIENTO','010')
INSERT INTO #temp VALUES ('CARTA DE NATURALIZACIÓN','020')
INSERT INTO #temp VALUES ('IDENTIFICACIÓN OFICIAL','030')
INSERT INTO #temp VALUES ('COMPROBANTE DE DOMICILIO','040')
INSERT INTO #temp VALUES ('CURP','050')
INSERT INTO #temp VALUES ('ACTA DE MATRIMONIO','060')
INSERT INTO #temp VALUES ('CONTRATO DE AHORRO','070')
INSERT INTO #temp VALUES ('ESCRITURA CONSTITUTIVA','110')
INSERT INTO #temp VALUES ('CÉDULA DE IDENTIFIC. FISCAL','120')
INSERT INTO #temp VALUES ('INSCRIPCIÓN REGISTRO PUBLICO','130')
INSERT INTO #temp VALUES ('IDENT.OFICIAL REPRESENT.LEGAL','140')
INSERT INTO #temp VALUES ('ESTADOS FINANCIEROS','150') 
 
 
INSERT INTO #temp2 VALUES ('00000003','070')
INSERT INTO #temp2 VALUES ('00000004','070')
INSERT INTO #temp2 VALUES ('00000005','030')
INSERT INTO #temp2 VALUES ('00000005','040')
INSERT INTO #temp2 VALUES ('00000005','060')
INSERT INTO #temp2 VALUES ('00000005','070')
INSERT INTO #temp2 VALUES ('00000006','070')
INSERT INTO #temp2 VALUES ('00000011','070') 
 
 
SELECT t3.descripcion, t3.cod_doc,t3.cod_soc,CASE WHEN t4.cod_soc IS NULL THEN 'No' ELSE 'Si' END AS entregado FROM(
SELECT * FROM #temp AS t1, (SELECT DISTINCT cod_soc FROM #temp2) AS t2
) t3 
FULL OUTER JOIN #temp2 AS t4 ON (t4.cod_doc=t3.cod_doc AND t3.cod_soc=t4.cod_soc)

saludos!!

Andres95 · #5 (**permalink**) 31/08/2012, 12:31

Así es..

Seria útil si por alguna razón el cliente pudiera tener relacionados documentos (#temp2) cuyas claves no existan en el catalogo de documentos (#temp)..

Saludos!

Libras · #6 (**permalink**) 31/08/2012, 12:48

El query lo que obtiene primero es el producto cartesiano de #temp1 junto con los usuarios distintos de #temp2 (para obtener todos los documentos disponibles para cada usuario) ya despues se hace un full outer join contra #temp2(donde especifica que documentos tiene el usuario) y se obtiene una lista donde viene si el docto ya lo entrego o no......

Código SQL:

Ver originalCREATE TABLE #temp
(
descripcion VARCHAR(100),
cod_doc VARCHAR(20)
)
 
CREATE TABLE #temp2
(
cod_soc VARCHAR(20),
cod_doc VARCHAR(20)
)
 
 
INSERT INTO #temp VALUES ('ACTA DE NACIMIENTO','010')
INSERT INTO #temp VALUES ('CARTA DE NATURALIZACIÓN','020')
INSERT INTO #temp VALUES ('IDENTIFICACIÓN OFICIAL','030')
INSERT INTO #temp VALUES ('COMPROBANTE DE DOMICILIO','040')
INSERT INTO #temp VALUES ('CURP','050')
INSERT INTO #temp VALUES ('ACTA DE MATRIMONIO','060')
INSERT INTO #temp VALUES ('CONTRATO DE AHORRO','070')
INSERT INTO #temp VALUES ('ESCRITURA CONSTITUTIVA','110')
INSERT INTO #temp VALUES ('CÉDULA DE IDENTIFIC. FISCAL','120')
INSERT INTO #temp VALUES ('INSCRIPCIÓN REGISTRO PUBLICO','130')
INSERT INTO #temp VALUES ('IDENT.OFICIAL REPRESENT.LEGAL','140')
INSERT INTO #temp VALUES ('ESTADOS FINANCIEROS','150') 
 
 
INSERT INTO #temp2 VALUES ('00000001','070')
INSERT INTO #temp2 VALUES ('00000003','070')
INSERT INTO #temp2 VALUES ('00000004','070')
INSERT INTO #temp2 VALUES ('00000005','030')
INSERT INTO #temp2 VALUES ('00000005','040')
INSERT INTO #temp2 VALUES ('00000005','060')
INSERT INTO #temp2 VALUES ('00000005','010')
INSERT INTO #temp2 VALUES ('00000005','070')
INSERT INTO #temp2 VALUES ('00000006','070')
INSERT INTO #temp2 VALUES ('00000011','070') 
 
 
SELECT t3.descripcion, t3.cod_doc,t3.cod_soc,CASE WHEN t4.cod_soc IS NULL THEN 'No' ELSE 'Si' END AS entregado FROM(
SELECT * FROM #temp AS t1, (SELECT DISTINCT cod_soc FROM #temp2) AS t2
) t3 
FULL OUTER JOIN #temp2 AS t4 ON (t4.cod_doc=t3.cod_doc AND t3.cod_soc=t4.cod_soc)

Probe agregando un usuario y un documento y me regresa la informacion como la esta pidiendo el compañero....

ACTA DE NACIMIENTO 010 00000001 No
CARTA DE NATURALIZACIÓN 020 00000001 No
IDENTIFICACIÓN OFICIAL 030 00000001 No
COMPROBANTE DE DOMICILIO 040 00000001 No
CURP 050 00000001 No
ACTA DE MATRIMONIO 060 00000001 No
CONTRATO DE AHORRO 070 00000001 Si
ESCRITURA CONSTITUTIVA 110 00000001 No
CÉDULA DE IDENTIFIC. FISCAL 120 00000001 No
INSCRIPCIÓN REGISTRO PUBLICO 130 00000001 No
IDENT.OFICIAL REPRESENT.LEGAL 140 00000001 No
ESTADOS FINANCIEROS 150 00000001 No
ACTA DE NACIMIENTO 010 00000003 No
CARTA DE NATURALIZACIÓN 020 00000003 No
IDENTIFICACIÓN OFICIAL 030 00000003 No
COMPROBANTE DE DOMICILIO 040 00000003 No
CURP 050 00000003 No
ACTA DE MATRIMONIO 060 00000003 No
CONTRATO DE AHORRO 070 00000003 Si
ESCRITURA CONSTITUTIVA 110 00000003 No
CÉDULA DE IDENTIFIC. FISCAL 120 00000003 No
INSCRIPCIÓN REGISTRO PUBLICO 130 00000003 No
IDENT.OFICIAL REPRESENT.LEGAL 140 00000003 No
ESTADOS FINANCIEROS 150 00000003 No
ACTA DE NACIMIENTO 010 00000004 No
CARTA DE NATURALIZACIÓN 020 00000004 No
IDENTIFICACIÓN OFICIAL 030 00000004 No
COMPROBANTE DE DOMICILIO 040 00000004 No
CURP 050 00000004 No
ACTA DE MATRIMONIO 060 00000004 No
CONTRATO DE AHORRO 070 00000004 Si
ESCRITURA CONSTITUTIVA 110 00000004 No
CÉDULA DE IDENTIFIC. FISCAL 120 00000004 No
INSCRIPCIÓN REGISTRO PUBLICO 130 00000004 No
IDENT.OFICIAL REPRESENT.LEGAL 140 00000004 No
ESTADOS FINANCIEROS 150 00000004 No
ACTA DE NACIMIENTO 010 00000005 Si
CARTA DE NATURALIZACIÓN 020 00000005 No
IDENTIFICACIÓN OFICIAL 030 00000005 Si
COMPROBANTE DE DOMICILIO 040 00000005 Si
CURP 050 00000005 No
ACTA DE MATRIMONIO 060 00000005 Si
CONTRATO DE AHORRO 070 00000005 Si
ESCRITURA CONSTITUTIVA 110 00000005 No
CÉDULA DE IDENTIFIC. FISCAL 120 00000005 No
INSCRIPCIÓN REGISTRO PUBLICO 130 00000005 No
IDENT.OFICIAL REPRESENT.LEGAL 140 00000005 No
ESTADOS FINANCIEROS 150 00000005 No
ACTA DE NACIMIENTO 010 00000006 No
CARTA DE NATURALIZACIÓN 020 00000006 No
IDENTIFICACIÓN OFICIAL 030 00000006 No
COMPROBANTE DE DOMICILIO 040 00000006 No
CURP 050 00000006 No
ACTA DE MATRIMONIO 060 00000006 No
CONTRATO DE AHORRO 070 00000006 Si
ESCRITURA CONSTITUTIVA 110 00000006 No
CÉDULA DE IDENTIFIC. FISCAL 120 00000006 No
INSCRIPCIÓN REGISTRO PUBLICO 130 00000006 No
IDENT.OFICIAL REPRESENT.LEGAL 140 00000006 No
ESTADOS FINANCIEROS 150 00000006 No
ACTA DE NACIMIENTO 010 00000011 No
CARTA DE NATURALIZACIÓN 020 00000011 No
IDENTIFICACIÓN OFICIAL 030 00000011 No
COMPROBANTE DE DOMICILIO 040 00000011 No
CURP 050 00000011 No
ACTA DE MATRIMONIO 060 00000011 No
CONTRATO DE AHORRO 070 00000011 Si
ESCRITURA CONSTITUTIVA 110 00000011 No
CÉDULA DE IDENTIFIC. FISCAL 120 00000011 No
INSCRIPCIÓN REGISTRO PUBLICO 130 00000011 No
IDENT.OFICIAL REPRESENT.LEGAL 140 00000011 No
ESTADOS FINANCIEROS 150 00000011 No

No entiendo porque dices que no aplica para este caso.......

Andres95 · #7 (**permalink**) 31/08/2012, 13:04

Me refiero a la diferencia entre el full y el left..

Anexo como referencia esta liga, para algun otro usuario que desee mas detalle..
Usar combinaciones externas

El full se utiliza para traerte datos de dos fuentes uniendo aquellos que hagan match y a los que no se regresan como nulo del lado (izquierdo o derecho) en el que no exista la clave.

Si aseguramos que no habra ningun docto entregado por el usuario que este fuera del catalogo de doctos(con un FK por ejemplo), con un left se cubre la necesidad.. ya que se traera todos los doctos existentes y relacionando solo los doctos entregados por usuario... (el producto cartesiano no te lo quitas)

en tu ejemplo puedes verificar la diferencia agregando un insert mas a temp2 (doctos entregados)

Notaras que regresa resultados diferentes el Full vs Left..

Código:

INSERT INTO #temp2 values ('00000001','999')

Saludos!

Libras · #8 (**permalink**) 31/08/2012, 13:12

i got it :) si le agregamos el left join funciona a la perfeccion jejejeje

Código SQL:

Ver originalSELECT t3.descripcion, t3.cod_doc,t3.cod_soc,CASE WHEN t4.cod_soc IS NULL THEN 'No' ELSE 'Si' END AS entregado FROM(
SELECT * FROM #temp AS t1, (SELECT DISTINCT cod_soc FROM #temp2) AS t2
) t3 
LEFT JOIN #temp2 AS t4 ON (t4.cod_doc=t3.cod_doc AND t3.cod_soc=t4.cod_soc)

quedando de esta manera :)

saludos y gracias por la aclaracion

arcr · #9 (**permalink**) 31/08/2012, 15:35

Hola:

Quiero dar las gracias a Andres95 y Libras ambos querys me sirvieron perfectamente son unos genios para SQL aunque son complejos de entender para mi que soy una novata quisiera saber si pudieran explicarme un poco mas acerca de la consulta para comprenderla y sino es posible muchas gracias de todas maneras. Me gusto mucho este foro y las respuestas muy rapidas me salvaron la vida gracias de nuevo. Otra cosa si saben de alguna fuente buena para aprender mas sobre subconsultas, case y acerca de los diferentes tipos de join se los agradeceria para darles una buena lectura y asi aprender mas

Saludos

Libras · #10 (**permalink**) 31/08/2012, 16:08

querias obtener un producto cartesiano entre tus 2 tablas(esto es que para un usuario necesitabas que tuviera todos los productos) que eso se hace con

SELECT * FROM #temp as t1, (select distinct cod_soc from #temp2) as t2

(aqui puse un distinct para que no repitiera los numeros de usuario)

despues se tenia que sacar de todas las opciones cual estaba marcada y cual no para lo cual se hizo:

SELECT t3.descripcion, t3.cod_doc,t3.cod_soc,case when t4.cod_soc IS NULL then 'No' else 'Si' end AS entregado FROM(
SELECT * FROM #temp as t1, (select distinct cod_soc from #temp2) as t2)
t3
LEFT JOIN #temp2 as t4 on (t4.cod_doc=t3.cod_doc and t3.cod_soc=t4.cod_soc)

al final se hace un left join con la tabla de documentos para sacar cuales documentos ya fueron entregados :) y con un case comparas si el valor fue entregado o no :)

saludos!