Consulta algo especial

[LOD_Fredy]

Hola a todos, tenia rato que no pasaba por aqui, hoy respondi algunos temas de los que se esperando les sirva mi comentario.

Hoy vengo pidiendo ayuda de nuevo, no se mucho de mysql y me estoy viendo en la necesidad xD de realizar una consulta de la siguiente forma:

tengo las siguientes tablas

experiencia (ex_id,ex_descr)
habilidades(ha_id,ha_descr)
idiomas(id_id,id_descr)
personas(pe_id,pe_nombre,ci_id)
ciudad(ci_id,ci_descr)
personaexp(pe_id,ex_id)
personahab(pe_id,ha_id)
personaidi(pe_id,id_id)

las tres ultimas permiten a una persona tener mas de una experiencia, habilidad o idioma, ciudad tiene una cada persona.

supongamos los siguientes datos de las tablas

experiencia:
1,'contador'
2,'albañil'
3,'tenico automotriz'

habilidades:
1,'trabajo bajo presion'
2,'buen trato'

idiomas:
1,'español'
2,'ingles'
3,'frances'

personas:
1,'juan',2
2,'luis',1
3,'jorge',1

personaexp (recuerden son ids):
1,2
1,3
2,1
3,3

personahab:
1,1
3,1
3,2

personaidi:
1,1

El asunto esta en que se debe ordenar las personas a partir de cual tenga mas coincidencias en las tres ultimas tablas, para el ejemplo necesitaria que sucediera algo asi,

pe_id=1,pe_descr='juan'
pe_id=3,pe_descr='jorge'
pe_id=2,pe_descr='luis'

juan sale en las tres tablas
jorge en dos
luis en una

en caso de que no saliera en ninguna, no deberia aparecer en el resultado de la consulta. A esto le agrego que hay filtros, es decir, se va a buscar por sexo, experiencia y/o idioma, aunque supongo que teniendo la consulta anterior, seria nada mas poner condiciones en el where para que filtre. Estaria genial que ademas de ordenarlos asi, si hubiera 4 que salen en las 3 tablas, se checara ahora de las tablas, el que tuviera mas registros para ordenarlos de mayor a menor numero de registros (es mucho pedir, lo se xD)

Igual y la consulta esta sencilla pero la verdad le he dado vueltas al asunto y no tengo idea de como hacer que aparezcan asi, si todo estuviera en una misma tabla seria mas sencillo, pero desde tablas separadas no tengo idea.

Gracias y disculpen la molestia.

[quimfv]

Código MySQL:

SELECT pe_id,count(*) FROM personaexp GROUP BY pe_id;
 

Esto te da el numero de apariciones de una persona en "personaexp"

Código MySQL:

SELECT p.pe_id,p.pe_nombre
FROM personas p 
  INNER JOIN 
     (SELECT pe_id,count(*) as apariciones FROM personaexp GROUP BY pe_id
      UNION ALL
      SELECT pe_id,count(*) as apariciones FROM personahab GROUP BY pe_id
      UNION ALL
      SELECT pe_id,count(*) as apariciones FROM personaidi GROUP BY pe_id) as Sbc
    ON p.pe_id=Sbc.pe_id
GROUP BY p.pe_id,p.pe_nombre
ORDER BY SUM(Sbc.apariciones) DESC;
 

Era esto?

[LOD_Fredy]

Iniciado por quimfv

Código MySQL:

SELECT pe_id,count(*) FROM personaexp GROUP BY pe_id;
 

Esto te da el numero de apariciones de una persona en "personaexp"

Código MySQL:

SELECT p.pe_id,p.pe_nombre
FROM personas p 
  INNER JOIN 
     (SELECT pe_id,count(*) as apariciones FROM personaexp GROUP BY pe_id
      UNION ALL
      SELECT pe_id,count(*) as apariciones FROM personahab GROUP BY pe_id
      UNION ALL
      SELECT pe_id,count(*) as apariciones FROM personaidi GROUP BY pe_id) as Sbc
    ON p.pe_id=Sbc.pe_id
GROUP BY p.pe_id,p.pe_nombre
ORDER BY SUM(Sbc.apariciones) DESC;
 

Era esto?

Tu codigo funciona perfecto si solo quiero ver quien tiene mas registros entre las 3 tablas, pero antes de eso, necesito ordenar dependiendo de en que tablas salga la persona, por ejemplo

pedro sale en la tabla personaexp
juan sale en la tabla personahab y personaexp
luis sale en la tabla personaidi, personaexp

entonces el orden para mostrar el pe_id y pe_nombre seria
juan
luis
pedro

todo esto independientemente de cuantos registros de juan, pedro o luis haya.

si luis tambien estuviera en la tabla personahab, saldria en primer lugar y juan en segundo. Y de esto hay muchas variantes pues tambien habra filtros que deberan traerse los datos de las personas que coincidan con esos filtros.

Hacer todo lo que necesito en una sola consulta a mi no se me hace sencillo, obvio alguien con mas experiencia que yo en mysql puede que la saque igual y en un dia de quebrarse la cabeza, yo llevo 3 dias y nomas no logro lo que necesito, minimo lo de ordenar el pe_id y pe_nombre a partir de en cuantas tablas se encontro a la persona y ordenar de la que se encuentra en las 3, en 2 o en 1.

Gracias y pues, dejo un poco pendiente esto por que me salio otra chamba que es para mañana.

[LOD_Fredy]

Mira, esta es la consulta que se me ocurre para ordenar a las personas segun el numero de tablas en que aparezcan.

Código MySQL:

SELECT PE.pe_id,pe_nombre,pe_ape1,pe_ape2,pe_fenac,pe_sexo,EC.ec_descr 'Edo Civil',pe_nacion,pe_email1,pe_email2,pe_tel1,pe_tel2,pe_tel3,pe_tel4,pe_dircallenum,pe_colonia,CI.ci_descr,pe_liga
,IF(PX.px_id<>"",1,0),IF(PS.ps_id<>"",1,0),IF(PH.ph_id<>"",1,0),IF(PI.pi_id<>"",1,0) 
FROM personas PE 
INNER JOIN estadosciviles AS EC ON EC.`ec_id`=Pe.`ec_id`
INNER JOIN ciudades AS CI ON CI.ci_id=Pe.`ci_id`
LEFT JOIN personaeexperiencia AS PX ON PX.`pe_id`=Pe.`pe_id` 
LEFT JOIN personaescolaridad AS PS ON Ps.pe_id=Pe.`pe_id` 
LEFT JOIN personahabilidad AS PH ON Ph.`pe_id`=Pe.`pe_id`
LEFT JOIN personaidioma AS PI ON PI.`pe_id`=Pe.pe_id
LEFT JOIN experiencia AS EX ON EX.ex_id=Px.`ex_id`
LEFT JOIN escolaridad AS ES ON ES.`es_id`=PS.`es_id`
GROUP BY Pe.`pe_id` ORDER BY IF(PX.px_id<>"",1,0) DESC,IF(PS.ps_id<>"",1,0) DESC, IF(PH.ph_id<>"",1,0) DESC,IF(PI.pi_id<>"",1,0) DESC
 

Lo ideal seria poder mezclar esa consulta con la tuya para obtener la consulta perfecta ya que la mia simplemente ordena del que tiene los 4 1 (un 1 por cada tabla) al que tiene 3, 2, 1 pero si dentro de los que tienen 4 hay uno que tiene 3 registros de la tabla 1 por ejemplo y otro tiene 2, los podria ordenar mal.

Voy a usar mi consulta por mientras ya que es lo principal ahorita, pero como te digo, juntar tu consulta con la mia seria lo ideal.

Gracias.

[quimfv]

Código MySQL:

SELECT p.pe_id,
p.pe_nombre,
SUM(Sbc.tablas) as NTablas,
SUM(Sbc.apariciones) as NApariciones
FROM personas p 
  INNER JOIN 
     (SELECT pe_id,
       if(count(*)>=1,1,0) as tablas,
       count(*) as apariciones 
FROM personaexp GROUP BY pe_id
      UNION ALL
      SELECT pe_id,
      if(count(*)>=1,1,0) as tablas,
      count(*) as apariciones 
FROM personahab GROUP BY pe_id
      UNION ALL
      SELECT pe_id,
      if(count(*)>=1,1,0) as tablas,
      count(*) as apariciones 
FROM personaidi GROUP BY pe_id) as Sbc
    ON p.pe_id=Sbc.pe_id
GROUP BY p.pe_id,p.pe_nombre
ORDER BY NTablas DESC,NApariciones DESC;
 

Era esto....