consulta sacando datos de tres tablas

[emiliochu]

Hola, soy nuevo en el foro, paso a redactar mi problema:
Hay tres tablas:
Tbl_servicios(id_servicio, valor,...)
tbl_servicios_inmuebles (id_servicio_inmueble, codigo_servicio, codigo_inmueble_es, fecha_expiracion_servicio,...)
inmuebles(id_inmueble,titulo,...)

Se relacionan asi:
tbl_servicios.id_servicio - tbl_servicios_inmuebles.codigo_servicio
inmuebles.id_inmueble - tbl_servicios_inmuebles.codigo_inmueble_es


Quisiera sacar todos los registros de la tabla inmuebles (tengan o no registros relacionados en la tabla tbl_servicios_inmuebles)más la suma del campo valor de la tabla tbl_servicios donde el campo fecha_expiracion_servicio sea mayor a la fecha del sistema, todo esto agrupado por id_inmueble.

La siguiente consulta funciona bien, pero solo muestra los registros de la tabla inmuebles que tienen registros relacionados en la tabla tbl_servicios_inmuebles, con lo cual no me sirve, el objetivo es ordenar todos los inmuebles por la suma de los valores de los servicios que tengan contratados.

Código SQL:

SELECT inmuebles.*, COUNT(tbl_servicios_inmuebles.id_servicio_inmueble ) AS cuenta, 
SUM(tbl_servicios.valor )AS suma FROM inmuebles, tbl_servicios_inmuebles, tbl_servicios 
WHERE tbl_provincias.id_provincia=inmuebles.provincia AND 
tbl_municipios.id_municipio=inmuebles.municipio AND 
tbl_servicios.id_servicio=tbl_servicios_inmuebles.codigo_servicio AND 
tbl_servicios_inmuebles.codigo_inmueble_es=inmuebles.id_inmueble AND 
tbl_tipos_inmuebles.id_tipo_inmueble=inmuebles.cod_tipo_inmueble AND visible=1 
GROUP BY id_inmueble ORDER BY ponderacion DESC, fecha_insercion, id_inmueble DESC 
LIMIT 0,10
 

Lo último que estoy probando es esta consulta, como veis no añado nada sobre la fecha pero no va. ¿Podeis echarme una manita con esto?

Código SQL:

SELECT DISTINCT inmuebles.*, COUNT(c.codigo_inmueble_es) AS cuenta FROM `inmuebles`
LEFT JOIN `tbl_servicios_inmuebles` AS `c` ON `c`.`codigo_inmueble_es` = `inmuebles`.`id_inmueble` GROUP BY id_inmueble
LEFT JOIN tbl_servicios ON id_servicio=codigo_servicio 
GROUP BY codigo_inmueble_es GROUP BY id_inmueble
 

[/I]

[gnzsoloyo]

La siguiente consulta funciona bien, pero solo muestra los registros de la tabla inmuebles que tienen registros relacionados en la tabla tbl_servicios_inmuebles, con lo cual no me sirve, el objetivo es ordenar todos los inmuebles por la suma de los valores de los servicios que tengan contratados.

A mi juicio lo que planteas no tiene sentido. Creo que o nos lo estás planteando mal, o no has razonado correctamente el problema
Entendamos que tienes esto:
1) Una tabla de Inmuebles.
2) Una tabla de Servicios.
3) Una tabla que relaciona a ambas, y que representa los servicios contratados de un inmueble.

Si esto es correcto, es imposible sumar los valores de los servicios, si el inmueble no tiene contratados servicios., es decir, sólo pueden aparecer si están relacionados en la tabla tbl_servicios_inmuebles.
Ahora bien, si lo que quieres es que te devuelva todos los inmuebles, con todos los tipos de servicio, y el detalle de suma de cada servicio en un inmueble, tenga o no ese servicio, la consulta es algo absurda, ya que es prácticamente un producto cartesiano...

¿Estás seguro de lo que estás pidiendo?

[emiliochu]

Si, estoy seguro pero quizas no me haya explicado bien. Es para determinar el orden de la consulta. A cada servicio le asigno un valor y quiero hayar la suma de este campo en cada inmueble para utilizarlo como parametro de ordenacion, de forma que salgan antes en los listados los inmuebles con mas servicios o con servicios mas valiosos. Tambien es necesario que muestre todos los inmuebles aunque no tengan servicios contratados. Y que solo sume los valores de los servicios contratados y que no hayan expirado.

Es posible?

[gnzsoloyo]

Probemos:

Código MySQL:

SELECT 
    i.id_inmueble, 
    COUNT(c.codigo_inmueble_es) cuenta,
    ts.id_servicio,
    SUM(ts.valor) totalServicio
FROM 
    `inmuebles` I 
    LEFT JOIN `tbl_servicios_inmuebles` `c` ON I.`id_inmueble` = `c`.`codigo_inmueble_es` 
    INNER JOIN tbl_servicios s ON c.codigo_servicio  = ts.id_servicio
GROUP BY I.id_inmueble
ORDER BY 2 DESC;
 

[emiliochu]

Error
consulta SQL: Documentación

Código MySQL:

SELECT 
    i.id_inmueble, 
    COUNT(c.codigo_inmueble_es) cuenta,
    ts.id_servicio,
    SUM(ts.valor) totalServicio
FROM 
    `inmuebles` I 
    LEFT JOIN `tbl_servicios_inmuebles` `c` ON I.`id_inmueble` = `c`.`codigo_inmueble_es` 
    INNER JOIN tbl_servicios s ON c.codigo_servicio  = ts.id_servicio
GROUP BY I.id_inmueble
ORDER BY 2 DESC
 LIMIT 0, 25
 

MySQL ha dicho: Documentación

#1054 - Unknown column 'i.id_inmueble' in 'field list'

Da este problema en la ejecución en Mysql, ya me lo había dado antes en consultas con join mas sencillas.
El tipo de la tabla inmuebles es MyISAM mientras que el de las tablas tbl_servicios y tbl_servicios_inmuebles es InnoDB. ¿Es posible que sea de aquí el problema? ¿Deben ser todas del mismo tipo? ¿Cuál de ellos es más recomendable?

[gnzsoloyo]

Da este problema en la ejecución en Mysql, ya me lo había dado antes en consultas con join mas sencillas.
El tipo de la tabla inmuebles es MyISAM mientras que el de las tablas tbl_servicios y tbl_servicios_inmuebles es InnoDB. ¿Es posible que sea de aquí el problema? ¿Deben ser todas del mismo tipo? ¿Cuál de ellos es más recomendable?

Nada que ver...
Lee atentamente lo que te dice: "Columna 'i.id_inmueble' desconocida en la lista de campos."

Significa sencillamente que esa columna o no pertenece a la tabla del alias, o no existe en esa tabla.

Verifica el nombre.

[emiliochu]

Tienes razón, vamos avanzando!

he corregido la consulta y quedaría así:

Código MySQL:

SELECT i.id_inmueble, COUNT(c.codigo_inmueble_es) cuenta, ts.id_servicio, SUM(ts.valor) totalServicio 
FROM `inmuebles` i 
LEFT JOIN `tbl_servicios_inmuebles` `c` ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
INNER JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio 
GROUP BY i.id_inmueble 
ORDER BY 2 DESC
 

¿Qué nos falta?

Por una parte, solo devuelve 16 filas, cuando en la tabla inmuebles hay 55 registros. Está ignorando los registros de la tabla inmuebles que no tienen servicios asociados en la tabla tbl_servicios_inmuebles.

Por otra parte, no consideramos que el campo fecha_expiracion_servicio del servicio seleccionado en el LEFT JOIN sea mayor a la fecha actual.

¿Es posible hacerlo en una sola consulta? Y más importante: Si es posible, ¿Es eficiente o al menos más eficiente que utilizar varias consultas y hacer comparaciones en php?

Gracias gnzsoloyo.

[gnzsoloyo]

¿Qué nos falta?

El WHERE....

Código MySQL:

SELECT i.id_inmueble, COUNT(c.codigo_inmueble_es) cuenta, ts.id_servicio, SUM(ts.valor) totalServicio 
FROM `inmuebles` i 
    LEFT JOIN `tbl_servicios_inmuebles` `c` ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
    INNER JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio 
WHERE DATE(C.fecha_expiracion_servicio) >= CURDATE()
    OR `c`.`codigo_inmueble_es`  IS NULL
GROUP BY i.id_inmueble 
ORDER BY 2 DESC
 

[emiliochu]

Correcto!, devuelve 15 resultados, puesto que un inmueble tiene sus servicios expirados.

Sólo nos quedaría añadir al los resultados todos aquellos inmuebles que no tienen servicios asociados, ¿Combiene hacerlo en la misma consulta? ¿o mediante una union con otra consulta? ¿Que seria mas eficiente?

[gnzsoloyo]

Con el IS NULL debería estar devolviendo los que no tienen servicios asociados.
Eso si, ten cuidado con el orden de las tablas en el LEFT JOIN: Afecta el resultado.

[emiliochu]

Lo siento pero no doy con la tecla:

Código MySQL:

SELECT i.id_inmueble, COUNT(c.codigo_inmueble_es) cuenta, ts.id_servicio, SUM(ts.valor) totalServicio 
FROM `inmuebles` i 
    LEFT JOIN `tbl_servicios_inmuebles` `c` ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
    INNER JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio 
WHERE DATE(c.fecha_expiracion_servicio) >= CURDATE()
    OR `c`.`codigo_inmueble_es`  IS NULL
GROUP BY i.id_inmueble 
ORDER BY 2 DESC
 

En el INNER JOIN solo veo referenciada una tabla: tbl_servicios_inmuebles, y no veo forma de modificar el orden.
Además, eliminando la condición de la fecha en la clausula WHERE:

Código MySQL:

SELECT i.id_inmueble, COUNT(c.codigo_inmueble_es) cuenta, ts.id_servicio, SUM(ts.valor) totalServicio 
FROM `inmuebles` i LEFT JOIN `tbl_servicios_inmuebles` `c` ON i.`id_inmueble` = `c`.`codigo_inmueble_es`
INNER JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio 
WHERE `c`.`codigo_inmueble_es` IS NULL 
GROUP BY i.id_inmueble
 ORDER BY 2 DESC
 

devuelve un conjunto de valores vacío.

Se me ocurre extraer los datos que faltan en otra consulta, pero creo que no seria eficiente. ¿Es posible en solo una consulta?

[gnzsoloyo]

Si esto

Código MySQL:

SELECT i.id_inmueble, COUNT(c.codigo_inmueble_es) cuenta, ts.id_servicio, SUM(ts.valor) totalServicio 
FROM `inmuebles` i LEFT JOIN `tbl_servicios_inmuebles` `c` ON i.`id_inmueble` = `c`.`codigo_inmueble_es`
INNER JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio 
WHERE `c`.`codigo_inmueble_es` IS NULL 
GROUP BY i.id_inmueble
 ORDER BY 2 DESC
 

te devuelve un conjunto vacío, eso sólo podría darse si no hubiese inmuebles sin algún servicio. Pero de todos modos está mal escrito, porque en esa query estás buscando exclusivamente los inmuebles sin servicios, y no el resto.
Las condiciones de búsqueda deben ser entendidas con precisión. No te olvides jamás que el SQL nunca se equivoca. El que se equivoca es el que escribe las condiciones.

Si lo que necesitas es que estén todos los casos debe haber una condición tal que se cumpla, y otra que indica que no haya relaciones. NULL serán todos los casos de Inmbuebles que no se relacionan con servicios, pero la primera condición es mandatoria.
Por otro lado, recuerda que NULL en una columna, tiene un valor de ordenamiento, por lo cual el ORDER BY debe considerar ese caso también.

Yo te propondría:

Código MySQL:

SELECT 
    i.id_inmueble, 
    COUNT(c.codigo_inmueble_es) cuenta, 
    IFNULL(ts.id_servicio, '') id_servicio
    IFNULL(SUM(ts.valor), 0.0) totalServicio -- Esto es para que controle la salida por NULL
FROM `inmuebles` i 
    LEFT JOIN `tbl_servicios_inmuebles` c ON i.`id_inmueble` = `c`.`codigo_inmueble_es`
    INNER JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio 
WHERE TRUE -- Esto es para que puedan salir todos los relacionados con algo
    OR `c`.`codigo_inmueble_es` IS NULL -- Esto para que también salgan los no relacionados
GROUP BY i.id_inmueble
ORDER BY 2 DESC, 1 ASC
 

Una nota: Pongo INNER JOIN en el segundo JOIN porque para que se cumpla que haya relación con la segunda tabla, forzosamente tiene que existir un registro en la segunda tabla, y se cumpliría la relación.

[emiliochu]

Soy consciente de que esta consulta:

Código SQL:

SELECT i.id_inmueble, COUNT(c.codigo_inmueble_es) cuenta, ts.id_servicio, SUM(ts.valor) totalServicio 
FROM `inmuebles` i LEFT JOIN `tbl_servicios_inmuebles` `c` ON i.`id_inmueble` = `c`.`codigo_inmueble_es`
INNER JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio 
WHERE `c`.`codigo_inmueble_es` IS NULL 
GROUP BY i.id_inmueble
 ORDER BY 2 DESC
 

Solicita a la tabla inmuebles todos los registros que no tienen servicios asociados, la hice intencionadamente porque en la esa tabla hay 60 registros y sólo 16 de ellos tienen servicios asociados en la tbl_servicios_inmuebles. por tanto la consulta anterior debería arrojar 44 registros desde mi opinión y devuelve 0.

Por otra parte, la siguiente consulta,

Código SQL:

SELECT i.id_inmueble, COUNT(c.codigo_inmueble_es) cuenta, IFNULL(ts.id_servicio, 0) id_servicio, IFNULL(SUM(ts.valor), 0.0) totalServicio
FROM `inmuebles` i 
LEFT JOIN `tbl_servicios_inmuebles` c ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
INNER JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio WHERE TRUE OR `c`.`codigo_inmueble_es` IS NULL 
GROUP BY i.id_inmueble
ORDER BY 2 DESC, 1 ASC
 

Continúa mostrando tan solo 16 registros, según entiendo, en teoría debería mostrar todos los de la tabla inmuebles, pero en la práctica no es el caso.

¿Es posible que esto sea causado por algún defecto en la creación de la estructura de las tablas, tipos o algo similar?

[gnzsoloyo]

Vamos a dividir esa segunda consulta en partes, para ver qué sucede:
¿Cuántos y qué registros devuelve esto:

Código MySQL:

SELECT 
    i.id_inmueble, 
    COUNT(c.codigo_inmueble_es) cuenta
FROM `inmuebles` i 
    LEFT JOIN `tbl_servicios_inmuebles` c ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
WHERE TRUE OR i.`id_inmueble` IS NULL 
GROUP BY i.id_inmueble
ORDER BY 2 DESC, 1 ASC;
 

Esto otro:

Código MySQL:

SELECT 
    i.id_inmueble, 
    COUNT(c.codigo_inmueble_es) cuenta
FROM `inmuebles` i 
    LEFT JOIN `tbl_servicios_inmuebles` c ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
WHERE i.`id_inmueble` IS NULL 
GROUP BY i.id_inmueble
ORDER BY 2 DESC, 1 ASC;
 

Y esto también:

Código MySQL:

SELECT 
    c.`codigo_inmueble_es`, IFNULL(ts.id_servicio, 0) id_servicio, IFNULL(SUM(ts.valor), 0.0) totalServicio
FROM `tbl_servicios_inmuebles` c 
    LEFT  JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio 
WHERE TRUE OR ts.id_servicio IS NULL 
GROUP BY c.`codigo_inmueble_es`
ORDER BY 1 ASC;
 

[emiliochu]

Iniciado por gnzsoloyo

Vamos a dividir esa segunda consulta en partes, para ver qué sucede:
¿Cuántos y qué registros devuelve esto:

Código MySQL:

SELECT 
    i.id_inmueble, 
    COUNT(c.codigo_inmueble_es) cuenta
FROM `inmuebles` i 
    LEFT JOIN `tbl_servicios_inmuebles` c ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
WHERE TRUE OR i.`id_inmueble` IS NULL 
GROUP BY i.id_inmueble
ORDER BY 2 DESC, 1 ASC;
 

Esta consulta devuelve los 69 registros que tiene la tabla inmuebles, con el numero de servicios contratados que tiene cada uno de ellos.

Esto otro:

Código MySQL:

SELECT 
    i.id_inmueble, 
    COUNT(c.codigo_inmueble_es) cuenta
FROM `inmuebles` i 
    LEFT JOIN `tbl_servicios_inmuebles` c ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
WHERE i.`id_inmueble` IS NULL 
GROUP BY i.id_inmueble
ORDER BY 2 DESC, 1 ASC;
 

Esta consulta devuelve un conjunto de valores vacio

Y esto también:

Código MySQL:

SELECT 
    c.`codigo_inmueble_es`, IFNULL(ts.id_servicio, 0) id_servicio, IFNULL(SUM(ts.valor), 0.0) totalServicio
FROM `tbl_servicios_inmuebles` c 
    LEFT  JOIN tbl_servicios ts ON c.codigo_servicio = ts.id_servicio 
WHERE TRUE OR ts.id_servicio IS NULL 
GROUP BY c.`codigo_inmueble_es`
ORDER BY 1 ASC;
 

Esta consulta devuelve los 17 registros de la tabla inmuebles que tienen registros asociados en tbl_servicios_inmuebles y con la suma del valor de cada uno de los servicios ordenados por le id_inmueble.

[gnzsoloyo]

Lo siento, la segunda estaba mal planteada:

Código MySQL:

SELECT 
    i.id_inmueble, 
    COUNT(c.codigo_inmueble_es) cuenta
FROM `inmuebles` i 
    LEFT JOIN `tbl_servicios_inmuebles` c ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
WHERE `c`.`codigo_inmueble_es`  IS NULL 
GROUP BY i.id_inmueble
ORDER BY 2 DESC, 1 ASC;
 

[emiliochu]

Iniciado por gnzsoloyo

Lo siento, la segunda estaba mal planteada:

Código MySQL:

SELECT 
    i.id_inmueble, 
    COUNT(c.codigo_inmueble_es) cuenta
FROM `inmuebles` i 
    LEFT JOIN `tbl_servicios_inmuebles` c ON i.`id_inmueble` = `c`.`codigo_inmueble_es` 
WHERE `c`.`codigo_inmueble_es`  IS NULL 
GROUP BY i.id_inmueble
ORDER BY 2 DESC, 1 ASC;
 

Devuelve 52 registros, con el campo cuenta a 0

[emiliochu]

¿Creeis que es posible unificar estas tres consultas en una para poder mostrar todos los inmuebles ordenados por el valor de los servicios que tienen asociados?