A vueltas con un query

[Angel.Matilla]

Y va el tercer hilo (Filtrar resultados en un query y ¿Y por qué no funciona el ORDER BY?) con el mismo problema. He encontrado otra manera de sacar los resultados y esta vez funciona:

Código SQL [-]
SELECT A.Codigo, A.Nombre, COALESCE(SUM(B.Votos), 0) Votos, COALESCE(C.Electos, 0) Electos, C.Circunscripcion, D.Nombre
  FROM Partidos A, Resultados B
  LEFT JOIN NumElectos C ON B.CodPrv = C.CodPrv AND B.Proceso = C.Proceso AND C.Tipo = 'M' AND B.Partido = C.Partido,
  Poblacion D
 WHERE A.CodPrv = :PrvIns 
   AND B.Proceso = :Proceso AND A.CodPrv = B.CodPrv AND B.Partido = A.Codigo
   AND B.Mesa IN (SELECT Codigo FROM Mesas WHERE CodPrv = A.CodPrv AND Municipio = C.Circunscripcion)
   AND A.CodPrv = D.CodPrv AND C.Circunscripcion = D.Codigo
 GROUP BY A.Codigo, A.Nombre, Electos, C.Circunscripcion, D.Nombre
 ORDER BY D.Nombre, Votos DESC, Electos DESC, A.Nombre

Las tablas implicadas en el query son estas:

Código SQL [-]
CREATE TABLE PARTIDOS (
  CODPRV VARCHAR(3) CHARACTER SET ISO8859_1 DEFAULT '13' NOT NULL COLLATE ES_ES_CI_AI,
  CODIGO SMALLINT NOT NULL,
  NOMBRE VARCHAR(45) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
  SIGLA VARCHAR(10) CHARACTER SET ISO8859_1 COLLATE ES_ES_CI_AI,
  ACTIVO SMALLINT DEFAULT 1,
  COLOR INTEGER DEFAULT 0);

ALTER TABLE PARTIDOS ADD CONSTRAINT PK_PARTIDOS PRIMARY KEY (CODPRV,CODIGO);

Código SQL [-]
CREATE TABLE RESULTADOS (
  CODPRV VARCHAR(3) CHARACTER SET ISO8859_1 DEFAULT '13' NOT NULL COLLATE ES_ES_CI_AI,
  PROCESO INTEGER NOT NULL,
  MESA INTEGER NOT NULL,
  PARTIDO INTEGER DEFAULT 1 NOT NULL,
  VOTOS INTEGER);

ALTER TABLE RESULTADOS ADD CONSTRAINT PK_RESULTADOS PRIMARY KEY (CODPRV,PROCESO,MESA,PARTIDO);

Código SQL [-]
CREATE TABLE NUMELECTOS (
  CODPRV VARCHAR(3) CHARACTER SET ISO8859_1 DEFAULT '13' NOT NULL COLLATE ES_ES_CI_AI,
  PROCESO INTEGER NOT NULL,
  TIPO VARCHAR(2) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
  CIRCUNSCRIPCION INTEGER DEFAULT 1 NOT NULL,
  PARTIDO INTEGER DEFAULT 1 NOT NULL,
  CARGO INTEGER NOT NULL,
  ELECTOS INTEGER NOT NULL);

ALTER TABLE NUMELECTOS ADD CONSTRAINT PK_NUMELECTOS PRIMARY KEY (CODPRV,PROCESO,TIPO,CIRCUNSCRIPCION,PARTIDO,CARGO);

Código SQL [-]
CREATE TABLE POBLACION (
  CODPRV VARCHAR(3) CHARACTER SET ISO8859_1 DEFAULT '13' NOT NULL COLLATE ES_ES_CI_AI,
  CODIGO INTEGER NOT NULL,
  NOMBRE VARCHAR(65) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
  CPOSTAL VARCHAR(10) CHARACTER SET ISO8859_1 NOT NULL COLLATE ES_ES_CI_AI,
  PAIS VARCHAR(2) CHARACTER SET ISO8859_1 DEFAULT 'ES' COLLATE ES_ES_CI_AI,
  CODINE VARCHAR(6) CHARACTER SET ISO8859_1 COLLATE ES_ES_CI_AI,
  JUDICIAL INTEGER DEFAULT 1);

ALTER TABLE POBLACION ADD CONSTRAINT PK_POBLACION PRIMARY KEY (CODPRV,CODIGO);

Código SQL [-]
CREATE TABLE MESAS (
  CODPRV VARCHAR(3) CHARACTER SET ISO8859_1 DEFAULT '13' NOT NULL COLLATE ES_ES_CI_AI,
  CODIGO INTEGER NOT NULL,
  MUNICIPIO INTEGER DEFAULT 1 NOT NULL,
  DISTRITO SMALLINT NOT NULL,
  SECCION SMALLINT NOT NULL,
  MESA VARCHAR(2) CHARACTER SET ISO8859_1 DEFAULT 'U' NOT NULL COLLATE ES_ES_CI_AI);

Puede darse la circunstancia de que no se hayan grabado o calculado todavía los electos (tabla NumElectos) para una determinado proceso y/o circunscripción. He hecho la prueba borrando todas las entradas de esa tabla para un determinado proceso y al ejecutar el query no me devuelve ninguna fila aunque el resto de tablas implicadas sí tengan información.

Yo tenía la idea que con el LEFT JOIN se obtenía resultados aunque en esa tabla no hubiera información que se ajustara a las condiciones. Si estoy equivocado os rogaría que me iluminaráis. Gracias.

[[duilioisola]]

En este caso debes agregar la condición a la parte de la UNION entre tablas (ON ...)
De esta forma hace el JOIN y como es de tipo LEFT no importa que no tenga datos...

Algo así... pero tendrías que utilizar un LEFT JOIN para RESULTADOS porque filtras por mesas que estén en la circunscripcion.

Código SQL [-]
select a.codigo, a.nombre, coalesce(sum(b.votos), 0) votos, coalesce(c.electos, 0) electos, c.circunscripcion,
       d.nombre
from partidos a, resultados b, poblacion d
left join numelectos c on b.codprv = c.codprv and b.proceso = c.proceso and c.tipo = 'M' and b.partido = c.partido and d.codigo = c.circunscripcion /* paso aqui la condicion */
where
a.codprv = : prvins and
b.proceso = : proceso and
a.codprv = b.codprv and
b.partido = a.codigo and
b.mesa in (select codigo
           from mesas
           where
           codprv = a.codprv and
           municipio = c.circunscripcion) and
a.codprv = d.codprv
/* and c.circunscripcion = d.codigo */
group by a.codigo, a.nombre, electos, c.circunscripcion, d.nombre
order by d.nombre, votos desc, electos desc, a.nombre

Yo te recomentadría utilizar JOIN / LEFT JOIN para unir las 4 tablas y no mezclar diferentes formas de hacerlo.

[[duilioisola]]

Transformado a JOINS

Código SQL [-]
select a.codigo, a.nombre, coalesce(sum(b.votos), 0) votos, coalesce(c.electos, 0) electos, c.circunscripcion,
       d.nombre
from partidos a
join resultados b on b.partido = a.codigo and a.codprv = b.codprv
join poblacion d on a.codprv = d.codprv
left join numelectos c on b.codprv = c.codprv and b.proceso = c.proceso and c.tipo = 'M' and b.partido = c.partido and d.codigo = c.circunscripcion
where
a.codprv = : prvins and
b.proceso = : proceso and
/* Que pasa si no hay ninguna mesa con esa circunscripcion */
b.mesa in (select codigo
           from mesas
           where
           codprv = a.codprv and
           municipio = c.circunscripcion)
group by a.codigo, a.nombre, electos, c.circunscripcion, d.nombre
order by d.nombre, votos desc, electos desc, a.nombre

[Angel.Matilla]

Empezado por duilioisola

Transformado a JOINS

Gracias por la sugerencia. Tengo que estudiarla porque tal cual me la has planteado he tendio que abortar la ejecución porque llevaba 10 minutos sin sacar nada y tal como he planteado el query yo se ejecutaba en menos de 1 segundo.

[Angel.Matilla]

Empezado por duilioisola

Transformado a JOINS

Gracias por la idea. He estado probando y si ejecuto el query tal como me propones, como te he dicho antes, se eterniza y tengo que cancelar la ejecución porque llevando 10 minutos no ha sacado nada, y es lógico porque, a mi modesto entender, trataría de sacar todas las filas de la tabla poblacion; y si hago un pequeño cambio:

Código SQL [-]
from partidos a
join resultados b on b.partido = a.codigo and a.codprv = b.codprv
left join numelectos c on b.codprv = c.codprv and b.proceso = c.proceso and c.tipo = 'M' and b.partido = c.partido
join poblacion d on a.codprv = d.codprv and d.codigo = c.circunscripcion

Si no hay datos en NumElectos no saca nada. Seguiré investigando, pero ya mañana.

[[duilioisola]]

Si haces LEFT JOIN con numelectos, también debes hacer LEFT JOIN con las tablas que se unen a ella.
Si numelectos no tiene registros, C.CIRCUNSCRIPCION será nulo y el JOIN con población no devolverá nada.

Código SQL [-]
from partidos a
join resultados b on b.partido = a.codigo and a.codprv = b.codprv
left join numelectos c on b.codprv = c.codprv and b.proceso = c.proceso and c.tipo = 'M' and b.partido = c.partido
*LEFT* join poblacion d on a.codprv = d.codprv and d.codigo = c.circunscripcion

Código SQL [-]
from partidos a
join resultados b on b.partido = a.codigo and a.codprv = b.codprv
left join numelectos c on b.codprv = c.codprv and b.proceso = c.proceso and c.tipo = 'M' and b.partido = c.partido
*LEFT* join poblacion d on a.codprv = d.codprv and d.codigo = c.circunscripcion
*LEFT JOIN* mesas on  codprv = a.codprv and municipio = c.circunscripcion

Para que no se haga eterno deberás tener índices para los campos con los que haces el JOIN.
He visto que tienes PKs, que generan índices para estas tablas, pero que quizás no sean óptimos.

Por ejemplo

Quizás NUMELECTOS debería cambiar el orden de los campos de la PK a CODPRV,PROCESO,TIPO,[PARTIDO <- , -> CIRCUNSCRIPCION],CARGO para que se utilice mejor en el LEFT JOIN.
POBLACION debería tener un índice por CODPRV,CODIGO.
MESAS no tiene ningún índice.

[Angel.Matilla]

Gracias por la orientación.

[Angel.Matilla]

Empezado por duilioisola

Quizás NUMELECTOS debería cambiar el orden de los campos de la PK a CODPRV,PROCESO,TIPO,[PARTIDO <- , -> CIRCUNSCRIPCION],CARGO para que se utilice mejor en el LEFT JOIN.
POBLACION debería tener un índice por CODPRV,CODIGO.
MESAS no tiene ningún índice.

Perdona por no haber puesto todo.
POBLACION tiene como clave primaria precisamente esa.
MESAS tiene como clave primaria CODPRV, CODIGO. Además tiene otro índice con CODPRV y MUNICIPIO.

[Angel.Matilla]

Empezado por duilioisola

Si haces LEFT JOIN con numelectos, también debes hacer LEFT JOIN con las tablas que se unen a ella.
Si numelectos no tiene registros, C.CIRCUNSCRIPCION será nulo y el JOIN con población no devolverá nada.

Ahora sí. Muchísimas gracias.