Problema con mysql

VenenoxHC · 18 Dic 2012

Sammy dijo:
la vdd que no entiendo mxo porke kieres limitar la COD_EVALUACION
mejor podrias poner que quieres conseguir a traves de la consulta, logre crear la base de datos que subiste y una idea me he hecho, pero no me keda del todo claro)

[TABLE="width: 623"]
[TR]
[TD]ACTXPRO
[/TD]
[TD][/TD]
[TD][/TD]
[TD]ACTxEVA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cod_profesion[/TD]
[TD]cod_actividad[/TD]
[TD][/TD]
[TD]cod_evaluacion[/TD]
[TD]cod_actividad[/TD]
[TD]cantidad[/TD]
[/TR]
[TR]
[TD="align: left"]1[/TD]
[TD]1[/TD]
[TD]
[/TD]
[TD="align: left"]

1

[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]FUNXPRO[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]rut_funcionario[/TD]
[TD]cod_profesion[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9[/TD]
[TD][/TD]
[TD="align: left"]

1111111

[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

Haciendo tu consulta
1-SELECT AP.cod_actividad, AE.cod_actividad
2-FROM ACTXPRO AP
3-LEFT JOIN ACTXEVA AE ON AP.cod_actividad = AE.cod_actividad
4-INNER JOIN FUNXPRO FP ON AP.cod_profesion = FP.cod_profesion
5-WHERE FP.rut_funcionario=11111111
6-AND AE.cod_evaluacion=1 <-- SIN esta linea retorna LO SIGUIENTE

[TABLE="class: ajax pma_table"]
[TR]
[TH="class: draggable column_heading pointer marker, align: left"] cod_actividad
[/TH]
[TH="class: draggable column_heading pointer marker, align: left"] cod_actividad
[/TH]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]1[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]2[/TD]
[TD="class: data grid_edit nowrap, align: right"]2[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]3[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]4[/TD]
[TD="class: data grid_edit nowrap, align: right"]4[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]5[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]6[/TD]
[TD="class: data grid_edit nowrap, align: right"]6[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]7[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]8[/TD]
[TD="class: data grid_edit nowrap, align: right"]8[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]9[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]10[/TD]
[TD="class: data grid_edit nowrap, align: right"]10[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]15[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]16[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]17[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]18[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]19[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]20[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[/TABLE]

QUE AL PARECER ES LO KE BUSCAS
PERO...AL LIMITARLO EN EL WHERE COD_EVALUACION=1, lo condicionas a que te muestre un solo resultado, x eso te quita todo lo demas y los NULL...Pero si lo consicionas en la relacion, te mostrara el "conjunto" con tu condicion
------------------------------
SELECT AP.cod_actividad,AE.cod_actividad, AE.cod_evaluacion, AE.cantidad
FROM ACTXPRO AP
LEFT OUTER JOIN ACTXEVA AE ON AE.cod_actividad = AP.cod_actividad AND (AE.cod_evaluacion=1 OR AE.cod_evaluacion IS NULL)
INNER JOIN FUNXPRO FP ON FP.cod_profesion = AP.cod_profesion
WHERE FP.rut_funcionario=11111111
ORDER BY AP.cod_actividad;

lo que esta con naranjo lo puedes manejar con una variable entrante
Consigues lo stge al limitarla con cod_evaluacion = 1:
[TABLE="class: ajax pma_table"]
[TR]
[TH="class: draggable column_heading pointer marker"] cod_actividad [/TH]
[TH="class: draggable column_heading pointer marker"] cod_actividad[/TH]
[TH="class: draggable column_heading pointer marker"] cod_evaluacion[/TH]
[TH="class: draggable column_heading pointer marker"] cantidad[/TH]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]1[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]2[/TD]
[TD="class: data grid_edit nowrap, align: right"]2[/TD]
[TD="class: data grid_edit nowrap, align: right"]1[/TD]
[TD="class: data grid_edit nowrap, align: right"]1[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]3[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]4[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]5[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]6[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]7[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]8[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]9[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]10[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]15[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]16[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]17[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]18[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: odd"]
[TD="class: data grid_edit not_null nowrap, align: right"]19[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[TR="class: even"]
[TD="class: data grid_edit not_null nowrap, align: right"]20[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[TD="class: data grid_edit null, align: right"]NULL[/TD]
[/TR]
[/TABLE]

o simplemente:
SELECT AP.cod_profesion,AP.cod_actividad, AE.cod_actividad
FROM ACTXPRO AP
INNER JOIN FUNXPRO FP ON AP.cod_profesion = FP.cod_profesion
LEFT JOIN ACTXEVA AE ON AP.cod_actividad = AE.cod_actividad AND AE.cod_evaluacion=1
WHERE FP.rut_funcionario=11111111;

ESPERO QUE TE HAYA PODIDO AYUDAR EN ALGO...
igual verifica bien si sirve y prueba con muchos mas datos, para que estes seguro de que te servira

si necesitas resolver mas dudas, intenta subir mas datos para las tablas que estas trabajando para poder probar con mejores ejemplos...

pd.disculpa las mayusculas pero nitaba que se destacara.

Bien wn, eso es exactamente lo que yo necesitaba, bueno yo ya lo había terminado y a eso venia al foro a subir la solución XD,, pero me encontré con tu respuesta la cual concuerda claramente con lo que yo hice.

Bueno antes de todo darte las GRACIAS con mayúscula por tu buena disposición hacia mi y estoy claro que lo harías por cualquier portalino, Mis sinceras gracias.

Lo que yo hice fue

SELECT A.cod_actividad as codact, A.nombre as nomact, AE.cod_actividad as codact2, AE.cantidad as cant
FROM ACTXPRO AS AP ,FUNXPRO AS FP,ACTIVIDAD AS A
LEFT JOIN ACTXEVA AE ON A.cod_actividad=AE.cod_actividad AND AE.cod_evaluacion='".$cod."'
WHERE A.cod_actividad=AP.cod_actividad
AND AP.cod_profesion=FP.cod_profesion
AND FP.rut_funcionario='".$rut."'
AND A.tipo=1
AND A.cod_actividad!=1
AND A.cod_actividad!=2
ORDER BY A.nombre ASC

la clave estaba en dar la condición en el mismo join.

Para explicarte lo que yo quería comienzo diciendo que al crear un funcionario se le asina una profesión y dicha profesión tiene N actividades,

este funcionario crea una evaluación a un paciente, en dicha evaluación el profesional escoge N actividades hechas al paciente, de su lista de actividades valga la redundancia.
Cachai?

LA TABLA EVALUACIÓN FINALMENTE ESTABA DE MAS.

cavoso · 20 Dic 2012

que bueno que pudiste solucionarlo xD, te iva a ayudar pero no me dio ni el tiempo de revisar tu base de datos

VenenoxHC · 21 Dic 2012

cavoso dijo:
que bueno que pudiste solucionarlo xD, te iva a ayudar pero no me dio ni el tiempo de revisar tu base de datos

Igualmente gracias, yo se que ud siempre esta dispuesto a todo, UIIIIIIIIIIIIIIIII!!!

Buscar

Problema con mysql

VenenoxHC

Usuario Casual nvl. 2

cavoso

Usuario Casual nvl. 2

VenenoxHC

Usuario Casual nvl. 2