[SOLUCIONADO] saber que campo tengo de una tabla en otra

[catpaw]

hola chic@s necesito su apoyo:

Veran, tengo 2 tablas, la primera es como de un inventario de muebles donde se repiten la clave de los muebles dependiendo de la bodega Y de la sucursal donde este localizado el mueble.

La segunda contiene la ruta Y el nombre de la foto por bodega, sucursal Y codigo del mueble:

t_mobiliario:

Código:

id	clave	descripcion	sucursal	bodega	existencia	importe
1	5	mesa circular 	100	1000	669	1169.12
2	5	mesa circular 	200	1000	0	0
3	4	modulo ejecutivo	200	1000	0	0
4	4	modulo ejecutivo	200	3000	812	15481.88
5	4	modulo ejecutivo	300	1000	940	139959.5

t_mobiliario_fotos

Código:

id	clave	sucursal	bodega	nombre	ruta
1	4	200	1000	clave_4_1.JPG	fotos/200/clave_4/
2	4	200	1000	clave_4_2.JPG	fotos/200/clave_4/
3	4	200	1000	clave_4_3.JPG	fotos/200/clave_4/
4	4	200	1000	clave_4_4.JPG	fotos/200/clave_4/
5	4	200	1000	clave_4_5.JPG	fotos/200/clave_4/
6	4	200	1000	clave_4_6.JPG	fotos/200/clave_4/
7	4	200	1000	clave_4_7.JPG	fotos/200/clave_4/

Tenia la siguiente consulta, que me daba el total de existencias y costo de mueble por clave, esto lo hago con un DISTINCT:

Código MySQL:

SELECT DISTINCT clave, descripcion, SUM(existencia) AS existencia, SUM(importe) AS importe, sucursal, bodega 
FROM t_mobiliario 
GROUP BY clave 
ORDER BY importe DESC
 

Lo que me da por ejemplo, el siguiente resultado:

Código:

id	clave	descripcion	existencia	importe
1	5	mesa circular 	669	1169.12
2	4	modulo ejecutivo	1752	155441.38
n	n	…	…	…

Bueno pues lo que necesito es una consulta que en mi resultado actual me agregue una marca o algo, que me diga que claves de muebles tienen foto, por lo que intente la siguiente consulta, que parecia funcionar pero me eleva o calcula incorrectamente las existencias e importes:

Código MySQL:

SELECT DISTINCT tm.clave, descripcion, SUM(existencia) AS existencia, SUM(importe) AS importe, tm.sucursal, tm.bodega, tf.id as foto FROM t_mobiliario tm LEFT JOIN t_mobiliario_fotos tf ON tf.clave=tm.clave GROUP BY tm.clave ORDER BY importe DESC
 

y por ejemplo me daba:

Código:

id	foto	clave	descripcion	existencia	importe
1	null	5	mesa circular 	4683	8183.84
2	1	4	modulo ejecutivo	12264	1088089.66
n	n	n	…	…	…

Sospecho que necesito una UNION pero sinceramente no se como ??

gracias de antemano

[JuJoGuAl]

y si pruebas primero sumando la cantidad de fotos? puesto haces una consulta sumando la existencia y el importe pero no sumas foto (y tienes que tomar en cuenta que pueden existir varias fotos para una sola clave)

Código MySQL:

SELECT DISTINCT 
tm.clave,
descripcion,
SUM(existencia) AS existencia,
SUM(importe) AS importe,
tm.sucursal,
tm.bodega,
SUM(tf.id) as foto
FROM t_mobiliario tm LEFT JOIN t_mobiliario_fotos tf
ON tf.clave=tm.clave
GROUP BY tm.clave
ORDER BY importe DESC
 

Prueba a ver si asi te suma bien los importes y existencias...

[catpaw]

hola jujoGuAl

probe como me propones pero las existencias e importe se siguen disparando :(

no se que este haciendo la consulta, si multiplicando existencia e importe por cada registro que se encuentra en la otra tabla o que

[JuJoGuAl]

Bueno me estoy concentrando un poco en tu data (dificil ejemplo si no poseo un esquema similar) y yo quitaria el DISTINTC de la consulta puesto si ya estas agrupando por clave, para que haces un distintc.

Código MySQL:

SELECT 
tm.clave,
descripcion,
SUM(existencia) AS existencia,
SUM(importe) AS importe,
tm.sucursal,
tm.bodega,
SUM(IF(tf.id != NULL,tf.id,0)) as foto
FROM t_mobiliario tm LEFT JOIN t_mobiliario_fotos tf
ON tf.clave=tm.clave
GROUP BY tm.clave
ORDER BY importe DESC
 

adicionalmente pongo un IF en la suma de fotos para comprobar si la clave tiene fotos, si no, coloca 0 (eso esquivaria los NULLS)

[catpaw]

hola jujoGuAl, gracias por tu tiempo

De acuerdo con lo del DISTINCT

Pero perdon, de que me sirve sumar los id de las fotos?

SUM(IF(tf.id != NULL,tf.id,0)) as foto

Código:

id	foto	clave	descripcion	existencia	importe
1	0	5	mesa circular 	4683	8183.84
2	924	4	modulo ejecutivo	12264	1088089.66
n	n	n	…	…	…

La consulta sigue igual, las existencias e importes no se han arreglado y lo unico que tengo diferente es que en el campo de foto en lugar de 1 obtengo la suma de nos que, que me da 924

:/

Debe ser la estructura de la consulta, tal vez necesite hacer primero una subconsulta o como lo decia antes una union, no lo se

[leonardo_josue]

Hola Catpaw:

Acláranos algunos puntos, ya que no me quedan claras cómo están definidas las relaciones entre tus tablas. En primer lugar, definamos la cardinalidad de tus tablas, es decir, un registro de tu tabla t_mobiliario puede tener 0 o n fotos asignadas t_mobiliario_fotos.

Con tus datos que pones de ejemplo, veo que sólo haces la unión por el campo CLAVE, es decir, en tu JOIN, pones esto:

Código:

...
FROM t_mobiliario tm LEFT JOIN t_mobiliario_fotos tf ON tf.clave=tm.clave
...

Sin embargo observa, en tu tabla t_mobiliarios hay 4 registros que tienen la clave = 4 (con diferente sucursal y bodega)... tal como lo estás haciendo se está formando un PRODUCTO CARTESIANO entre tus tablas: Observa este script:

Código MySQL:

mysql> SELECT * FROM t_mobiliario;
+------+-------+------------------+----------+--------+------------+----------+
| id   | clave | descripcion      | sucursal | bodega | existencia | importe  |
+------+-------+------------------+----------+--------+------------+----------+
|    1 |     5 | mesa circular    |      100 |   1000 |        669 | 1169.12  |
|    2 |     5 | mesa circular    |      200 |   1000 |          0 | 0        |
|    3 |     4 | modulo ejecutivo |      200 |   1000 |          0 | 0        |
|    4 |     4 | modulo ejecutivo |      200 |   3000 |        812 | 15481.88 |
|    5 |     4 | modulo ejecutivo |      300 |   1000 |        940 | 139959.5 |
+------+-------+------------------+----------+--------+------------+----------+
5 rows in set (0.03 sec)
 
mysql> SELECT * FROM t_mobiliario_fotos;
+------+-------+----------+--------+---------------+--------------------+
| id   | clave | sucursal | bodega | nombre        | ruta               |
+------+-------+----------+--------+---------------+--------------------+
|    1 |     4 |      200 |   1000 | clave_4_1.JPG | fotos/200/clave_4/ |
|    2 |     4 |      200 |   1000 | clave_4_2.JPG | fotos/200/clave_4/ |
|    3 |     4 |      200 |   1000 | clave_4_3.JPG | fotos/200/clave_4/ |
|    4 |     4 |      200 |   1000 | clave_4_4.JPG | fotos/200/clave_4/ |
|    5 |     4 |      200 |   1000 | clave_4_5.JPG | fotos/200/clave_4/ |
|    6 |     4 |      200 |   1000 | clave_4_6.JPG | fotos/200/clave_4/ |
|    7 |     4 |      200 |   1000 | clave_4_7.JPG | fotos/200/clave_4/ |
+------+-------+----------+--------+---------------+--------------------+
7 rows in set (0.00 sec)
 
mysql> SELECT tm.id, tm.clave, tm.descripcion, tf.id, tf.nombre, tf.ruta
    -> FROM t_mobiliario tm
    -> LEFT JOIN t_mobiliario_fotos tf ON tf.clave=tm.clave;
+------+-------+------------------+------+---------------+--------------------+
| id   | clave | descripcion      | id   | nombre        | ruta               |
+------+-------+------------------+------+---------------+--------------------+
|    1 |     5 | mesa circular    | NULL | NULL          | NULL               |
|    2 |     5 | mesa circular    | NULL | NULL          | NULL               |
|    3 |     4 | modulo ejecutivo |    1 | clave_4_1.JPG | fotos/200/clave_4/ |
|    3 |     4 | modulo ejecutivo |    2 | clave_4_2.JPG | fotos/200/clave_4/ |
|    3 |     4 | modulo ejecutivo |    3 | clave_4_3.JPG | fotos/200/clave_4/ |
|    3 |     4 | modulo ejecutivo |    4 | clave_4_4.JPG | fotos/200/clave_4/ |
|    3 |     4 | modulo ejecutivo |    5 | clave_4_5.JPG | fotos/200/clave_4/ |
|    3 |     4 | modulo ejecutivo |    6 | clave_4_6.JPG | fotos/200/clave_4/ |
|    3 |     4 | modulo ejecutivo |    7 | clave_4_7.JPG | fotos/200/clave_4/ |
|    4 |     4 | modulo ejecutivo |    1 | clave_4_1.JPG | fotos/200/clave_4/ |
|    4 |     4 | modulo ejecutivo |    2 | clave_4_2.JPG | fotos/200/clave_4/ |
|    4 |     4 | modulo ejecutivo |    3 | clave_4_3.JPG | fotos/200/clave_4/ |
|    4 |     4 | modulo ejecutivo |    4 | clave_4_4.JPG | fotos/200/clave_4/ |
|    4 |     4 | modulo ejecutivo |    5 | clave_4_5.JPG | fotos/200/clave_4/ |
|    4 |     4 | modulo ejecutivo |    6 | clave_4_6.JPG | fotos/200/clave_4/ |
|    4 |     4 | modulo ejecutivo |    7 | clave_4_7.JPG | fotos/200/clave_4/ |
|    5 |     4 | modulo ejecutivo |    1 | clave_4_1.JPG | fotos/200/clave_4/ |
|    5 |     4 | modulo ejecutivo |    2 | clave_4_2.JPG | fotos/200/clave_4/ |
|    5 |     4 | modulo ejecutivo |    3 | clave_4_3.JPG | fotos/200/clave_4/ |
|    5 |     4 | modulo ejecutivo |    4 | clave_4_4.JPG | fotos/200/clave_4/ |
|    5 |     4 | modulo ejecutivo |    5 | clave_4_5.JPG | fotos/200/clave_4/ |
|    5 |     4 | modulo ejecutivo |    6 | clave_4_6.JPG | fotos/200/clave_4/ |
|    5 |     4 | modulo ejecutivo |    7 | clave_4_7.JPG | fotos/200/clave_4/ |
+------+-------+------------------+------+---------------+--------------------+
23 rows in set (0.00 sec)
 

Observa que cada id de tu tabla t_mobiliario se une los 7 registros que tienen clave = 4 de tu tabla t_mobiliario_fotos... Esto es un producto cartesiano y por eso se están disparando tus sumas...

En primer lugar debes definir correctamente cómo están relacionadas tus tablas, es decir, considerar todas los campos que forman la llave foránea. Una vez que hagas esto, y dado que estás haciendo una agrupación, la relación entre tus tablas no debe ser de 0 a n, sino de 0 a 1... es decir, aunque un elemento del mobiliario TENGA MÁS DE UNA FOTO ASIGNADAS, tú sólo debes considerar un registro... puedes hacerlo con DISTINCT o con GROUP BY, según sea el caso...

Para el ejemplo, voy a considerar que efectivamente el campo CLAVE es el único por el que están relacionadas tus tablas... en primer lugar, para considerar sólo una foto lo puedo hacer con un DISTINCT:

Código:

mysql> SELECT DISTINCT clave FROM t_mobiliario_fotos;
+-------+
| clave |
+-------+
|     4 |
+-------+
1 row in set (0.01 sec)

Ahora sí, puedo hacer un LEFT JOIN sobre de esta subconsulta:

Código MySQL:

mysql> SELECT
    ->    tm.clave,
    ->    tm.descripcion,
    ->    SUM(tm.existencia) existencia,
    ->    SUM(tm.importe) importe,
    ->    tm.sucursal,
    ->    tm.bodega,
    ->    IFNULL(tf.clave, 'no tiene foto') foto
    -> FROM t_mobiliario tm
    -> LEFT JOIN
    ->    (SELECT DISTINCT clave FROM t_mobiliario_fotos) tf 
    ->    ON tm.clave = tf.clave
    -> GROUP BY tm.clave
    -> ORDER BY importe DESC;
+-------+------------------+------------+-----------+----------+--------+---------------+
| clave | descripcion      | existencia | importe   | sucursal | bodega | foto        |
+-------+------------------+------------+-----------+----------+--------+---------------+
|     4 | modulo ejecutivo |       1752 | 155441.38 |      200 |   1000 | 4        |
|     5 | mesa circular    |        669 |   1169.12 |      100 |   1000 | no tiene foto |
+-------+------------------+------------+-----------+----------+--------+---------------+
2 rows in set (0.00 sec)
 

Observa que la última columna agrega el código de la foto asignada o la leyenda NO TIENE FOTO...

Dale un vistazo para ver si es más o menos lo que necesitas.

Saludos
Leo.

[catpaw]

hola leo

un registro de tu tabla t_mobiliario puede tener 0 o n fotos asignadas t_mobiliario_fotos

-> esto es correcto

Con tus datos que pones de ejemplo, veo que sólo haces la unión por el campo CLAVE

-> Correcto

La relacion la hago por medio del campo clave al momento de la consulta

tal como lo estás haciendo se está formando un PRODUCTO CARTESIANO entre tus tablas

Sabia que algo raro esta pasando, pero ni idea que era y menos como arreglarlo. Gracias a tu ejemplo me queda claro

Dejame probar tu ejemplo y te comento...de momento me has dado mucha luz a mi problema gracias!!



------------------------> Edito

Ya lo probe leo y funciona ok, muchas gracias a los 2 por su tiempo

asi quedo:

Código MySQL:

SELECT tm.clave, tm.descripcion, SUM(tm.existencia) AS existencia, SUM(tm.importe) AS importe, tm.sucursal, tm.bodega, tf.clave AS foto 
FROM t_mobiliario tm 
LEFT JOIN (SELECT DISTINCT clave FROM t_mobiliario_fotos) tf ON tm.clave = tf.clave 
GROUP BY tm.clave
ORDER BY importe DESC
 

[JuJoGuAl]

Bueno eso demuestra que JuJo hoy esta sin neuronas, tampoco me habia percatado de eso... :D Gracias Leo!