consulta a 3 tablas

[yeyowave]

Buenas, compis, que tal lo llevamos el viernes... hoy espeso espeso que calor, haber si me echais un cable pero no al cuello jejeje..

tengo 3 tablas

produtos (id, nombre, stock)
1641, 'producto demo','25'

avstock_cab (id_prod,ultima_sol)
0, '20010230'
1641, '20120706'

avstock_det(id_prod, email)
1641, '[email protected]'
1641, '[email protected]'
1641, '[email protected]'
1641, '[email protected]'
0, '[email protected]'
0, '[email protected]'
0, '[email protected]'

nota: alomejor lo planteo mal y con 2 tablas tenia suficiente

select stc.id_prod,stc.ultima_sol,count(stod.id_prod) as esperan from avstock_cab stc inner join avstock_det stod on stc.id_prod = stod.id_prod group by stc.id_prod

0, '20010230', 3
1641, '20120706', 4

pero ahora necesito mostrar el nombre y el stock del producto, como meto un left join? puesto que si lo pongo asi.

select stc.id_prod,pr.nombre,stc.f_actualizado,count(stod .id_prod) as esperan from avstock_cab stc left join productos pr on pr.id = stc.id_prod inner join avstock_det stod on stc.id_prod = stod.id_prod group by stc.id_prod

0, '', '20010230', 6 <---- no son 6, son 3 los que tienen que salir
1641, 'producto demo', '20120706', 4

[quimfv]

Ni la una ni la otra son correctas creo...

Código MySQL:

SELECT p.id_prod,
             p.nombre, 
             p.stock,
             stc.ultima_sol,
             count(stod.id_prod) as esperan 
from (productos p left join avstock_cab stc ON p.id_prod=stc.id_prod)
                   left join avstock_det stod on stc.id_prod = stod.id_prod 
group by p.id_prod,
             p.nombre, 
             p.stock,
             stc.ultima_sol;
 

si no es esto, pon ejemplos coherentes de datos y quizas entenderemos lo que intentas hacer....

stc.f_actualizado no esta en la tabla...????

No se entiende mucho lo que haces ni el nombre de la tablas pero si

avstock_cab son las cabeceras de los pedidos

y

avstock_det el detalle de los pedidos

La relacion entre las dos tablas no puede ser el id_producto... la relacion tiene que ser por un idPedido que no tienes en la estructura.... si es así ni la query que te paso te dará lo que buscas, si con los datos que muestras pero no en general....

[yeyowave]

Hola, gracias por responder, no son pedidos, solicitudes de stock

- La tabla productos, es la tabla de los productos obiamente
- La tabla avstock_cab es la cabezera de los productuctos que tienen solicitudes de aviso, esta tabla tiene los campos id_prod = id de la tabla productos y el campo ultima_sol que es la ultima vez que un usuario estaba intersado por el producto.
- La tabla avstock_det tiene los campos id_prod que identifica al producto y el campo email que es el dato para avisar al cliente.

Ahora necesitamos listar los campos que hay en la tabla avstock_cab la ultima vez que se han interesado y la cantidad de solicitudes de stock que tienen.

la consulta seria
select stc.id_prod,stc.ultima_sol,count(stod.id_prod) as esperan from avstock_cab stc inner join avstock_det stod on stc.id_prod = stod.id_prod group by stc.id_prod

y tambien necesitamos listar el nombre del producto que hay en la tabla avstock_cab con la relacion de avstock_cab = productos.id y mostrar el nombre y el stock disponible.

Este es el gran reto, la consulta que has puesto tu y que te agradezco en el alma el tiempo que has dedicado a elaborarla, lista las solicitudes de todos los productos que hay en nuestra base de datos con el mismo problema que me pasa a mi, que salen 6 registros del producto con id 0 cuando deverian salir solo 3.

Creo que la solucion será crear una unica tabla que guarde los id de los productos y los emails de los clientes y realizar la consulta conjunta con la tabla productos y suprimir la tabla avstock_cab.

de todas formas si alguien tiene una idea mejor se lo agradeceria.

[yeyowave]

compi una vez mas gracias por tu tiempo, he sacado la consulta ok

select cab.id_prod,pr.dispo,pr.descrip,cab.ultima_sol,det .cantidad from (avstock_cab cab left join productos pr on cab.id_prod = pr.id) inner join (select id_prod,count(id_prod) as cantidad from avstock_det av group by av.id_prod) det on det.id_prod = cab.id_prod group by cab.id_prod

[gnzsoloyo]

Voy a meterme en este asunto para hacerte notar una cosa.
Con esta consulta:

Código MySQL:

SELECT
    stc.id_prod,
    stc.ultima_sol,
    count(stod.id_prod) esperan 
FROM 
    avstock_cab stc 
    INNER JOIN avstock_det stod 
    ON stc.id_prod = stod.id_prod 
GROUP BY stc.id_prod
 

y en base a los datos de ejemplo que aportas, sólo existe una posibilidad para que se cumpla que devuelva el doble de la cuenta que debería: Que haya en la tabla avstock_cab dos registros con id_prod = 0.
No existe otra posibilidad.

Lo que si te puedo hacer notar, además, es que es una falla absolutamente catastrófica que exista en la tabla avstock_cab un id de producto que no exista en la tabla productos. Un diseñador que permita que eso ocurra pierde automáticamente el trabajo, sin más explicaciones; y proponerlo como parte de un examen final de base de datos haría que los profesores no sólo te reprueben, sino que te echen a patadas de la sala.
¿Cómo es posible que tengas ese nivel de inconsistencia?
Nadie en su sano juicio propondría un esquema sin restricciones de FK que permitan evitar semejante basura.

[yeyowave]

chavalote, aqui la unica basura que hay es tu post y no es el primero que haces en ese tono, los datos son los que hay, y si no, copia el ejemplo tal cual y ejecutalo ya que de mis columnas seleccionadas tu tan experto que eres tan solo seleccionas 3 de las 5 campos a listar que o gran mago donde dejas la tabla de productos ehhh... quizas hagas magia para listar tambien en tu fantastico sql el nombre y el stock del producto, quizas por esta vida no hay que ser tan prepotente como lo eres tu porque de danta sabiduria te has vuelto un poco tonto, por otra parte el tema esta resuelto y no deverias haberte nisiquiera molestado en contestar o gran mago del mysql,php y otros temas que manejes, tus opiniones me las paso por el forro sinceramente, me da igual que seas o no moderador del foro, para mi cuenta mas la opinion modesta de un usuario que tiene 1 mensaje que el de una persona con mas de 10.000 que se dedica a faltar el respeto a los demas usuarios, quizas no te levantaras con buen pie en el dia de hoy, pero deverias plantearte el contestar temas con la prepotencia que lo haces.

[gnzsoloyo]

Dejando de lado tu ditraba, que ya te he respondido debidamente por vía administrativa, paso a demostrarte rápidamente que si me atengo a la descripción de tus tablas, y a los datos aportados en el primer post, la consulta que proponías inicialmente forzosamente debería dar corretamente el resultado.
Esta sería la creación de las tablas. No defino las FK que deberían ser necesarias, porque generaría un error al no existir el producto dado en la tabla, tal y como te lo plantee:

Código MySQL:

mysql> mysql> USE TEST;
Database changed
mysql>
mysql> DROP TABLE IF EXISTS productos;
Query OK, 0 rows affected (0.00 sec)
 
mysql> DROP TABLE IF EXISTS avstock_cab;
Query OK, 0 rows affected (0.00 sec)
 
mysql> DROP TABLE IF EXISTS avstock_det;
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> CREATE TABLE IF NOT EXISTS productos(
    ->     id INT UNSIGNED,
    ->     nombre VARCHAR(100),
    ->     stock INT UNSIGNED);
Query OK, 0 rows affected (0.03 sec)
 
mysql>
mysql> CREATE TABLE IF NOT EXISTS  avstock_cab(
    ->     id_prod INT UNSIGNED,
    ->     ultima_sol VARCHAR(50));
Query OK, 0 rows affected (0.00 sec)
 
mysql> CREATE TABLE IF NOT EXISTS  avstock_det(
    ->     id_prod INT UNSIGNED,
    ->     email VARCHAR(255));
Query OK, 0 rows affected (0.01 sec)
 
mysql> INSERT INTO productos
    -> VALUES(1641, 'producto demo',25);
Query OK, 1 row affected (0.01 sec)
 
mysql> INSERT INTO avstock_cab
    -> VALUES
    ->     (0, '20010233'),
    ->     (1641, '20120706');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO avstock_det
    -> VALUES
    ->     (1641, '[email protected]'),
    ->     (1641, '[email protected]'),
    ->     (1641, '[email protected]'),
    ->     (1641, '[email protected]'),
    ->     (0, '[email protected]'),
    ->     (0, '[email protected]'),
    ->     (0, '[email protected]');
Query OK, 7 rows affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0
 

Hecho esto, pasemos a tu consulta, que he escrito nada más que en una forma estructurada, sin sacarle ni ponerle nada:

Código MySQL:

mysql>
mysql> SELECT
    ->     stc.id_prod,
    ->     stc.ultima_sol,
    ->     count(stod.id_prod) esperan
    -> FROM
    ->     avstock_cab stc
    ->     INNER JOIN avstock_det stod
    ->     ON stc.id_prod = stod.id_prod
    -> GROUP BY stc.id_prod;
+---------+------------+---------+
| id_prod | ultima_sol | esperan |
+---------+------------+---------+
|       0 | 20010230   |       3 |
|    1641 | 20120706   |       4 |
+---------+------------+---------+
2 rows in set (0.00 sec)
 

Como es notorio, el resultado obtenido indica para el primer producto la cantidad de tres (3) que es precisamente lo que dices que se debe obtener, y que yo te dije que era lo que debía darte.
Ahora bien, probemos entonces agregando una operación más, con el id de producto en cero (0):

Código MySQL:

mysql> INSERT INTO avstock_cab
    -> VALUES
    ->     (0, '20010234');
Query OK, 1 row affected (0.06 sec)
 

O sea: Mismo producto, solicitud diferente.
El resultado:

Código MySQL:

mysql> SELECT
    ->     stc.id_prod,
    ->     stc.ultima_sol,
    ->     count(stod.id_prod) esperan
    -> FROM
    ->     avstock_cab stc
    ->     INNER JOIN avstock_det stod
    ->     ON stc.id_prod = stod.id_prod
    -> GROUP BY stc.id_prod;
+---------+------------+---------+
| id_prod | ultima_sol | esperan |
+---------+------------+---------+
|       0 | 20010233   |       6 |
|    1641 | 20120706   |       4 |
+---------+------------+---------+
2 rows in set (0.00 sec)
 

¿Quedó claro?
Se entiende el problema?
El error consiste en que el agrupamiento debería ser incluyendo el numero de solicitud, para evitar que se sumen valores incorrectamente.
Pero por sobre todo: No existe un error en la cuenta, sino que hay un registro adicional con ese ID del producto, que no pertenece al cálculo.

Fuera de eso, mantengo todo lo dicho, especialmente respecto a que no poner una restricción FK es diseñar basura.

[yeyowave]

haber, cuantas veces te tengo que decir que el resultado de tu consulta lo lista que no se ha de listar


tu estas haciendo la consulta solamente a las tablas avstock_cab y avstock_det, en ningun momento listas la tabla productos


Por lo tanto el resultado de la consulta que muestras no tiene nada que ver con lo que yo he solicitado


TE QUEDO CLARO!!!!!

[quimfv]

A ver, yeyowave, para sacar una lista de todos los productos y sus pedidos NO PUEDE DARSE QUE TENGAS un pedido sobre un producto que no existe (caso de los pedidos con id_prod igual a 0).

Que nombre y stock se puede poner al producto 0 si no esta en la tabla productos?
Si usas LEFT JOIN con la tabla productos a la izquierda el producto con id=0 NO VA A SALIR.


Como dice gnzsoloyo la unica manera de obtener ese 6 es tener dos veces el producto 0 en la tabla avstock_cab, y si hay 3 te saldrá 9.

Se puede decir más alto pero no más CLARO!!!

[gnzsoloyo]

Iniciado por yeyowave

tu estas haciendo la consulta solamente a las tablas avstock_cab y avstock_det, en ningun momento listas la tabla productos

No la listo porque esa tabla no contiene información de todos los productos, tal y como ya te lo aclaró quimfv.
Y en ese punto es donde estoy en total desacuerdo con tu diseño: Si PRODUCTOS es tabla base (tabla de la que dependen otras, pero no depende de ninguna), y es a donde se referencias las FK de otras tablas, debe contener forzosamente todos los productos, y si su ID es numérico, no puede contener ningún ID que sea cero.
Ahora bien, si aún así quieres usarla, te mostraré una curiosidad, usando tu propia consulta del primer post:

Código MySQL:

mysql> SELECT
    ->     stc.id_prod,
    ->     pr.nombre,
    -> --  stc.f_actualizado,
    ->     count(stod .id_prod) esperan
    -> FROM avstock_cab stc
    ->     LEFT JOIN productos pr ON pr.id = stc.id_prod
    ->     INNER JOIN avstock_det stod ON stc.id_prod = stod.id_prod
    -> GROUP BY stc.id_prod;
+---------+---------------+---------+
| id_prod | nombre        | esperan |
+---------+---------------+---------+
|       0 | NULL          |       3 |
|    1641 | producto demo |       4 |
+---------+---------------+---------+
2 rows in set (0.00 sec)
 

Como puedes ver, con tu propia consulta (la que posteas al principio), tampoco salen 6 unidades relacionadas con ese supuesto producto.
Nota: Tuve que comentar una de las columnas porque el campo mencionado no existe en tu descripción inicial, y por tanto no sé de qué tipo es.

Ahora bien, supongamos, tomando la aclaración de quimfv, que en lugar de poner el stock, ponemos las cosas como se debe, esto es, la tabla PRODUCTOS primero.
El resultado sería:

Código SQL:

mysql> SELECT
    ->     stc.id_prod,
    ->     pr.nombre,
    -> --  stc.f_actualizado,
    ->     COUNT(stod .id_prod) esperan
    -> FROM productos pr
    ->     LEFT JOIN avstock_cab stc ON pr.id = stc.id_prod
    ->     INNER JOIN avstock_det stod ON stc.id_prod = stod.id_prod
    -> GROUP BY stc.id_prod;
+---------+---------------+---------+
| id_prod | nombre        | esperan |
+---------+---------------+---------+
|    1641 | producto demo |       4 |
+---------+---------------+---------+
1 ROW IN SET (0.00 sec)
 

Se pone siempre primero la tabla PRODUCTOS en estos casos porque lo que se desea es un listado de productos de los que se requiere cierta información (las solicitudes), y no al revés... ¿Se entiende la lógica de eso?

Ahora bien, como ese pseudoproducto no existe, no aparecerá en el listado, y eso pone en evidencia la inconsistencia de datos de la base: No puede existir una operación registrada para un producto que no existe...
¿Se comprende?

Resumiendo:
1) La primera consulta que planteas (según los datos que aportas), devuelve tres unidades para el pseudoproducto.
2) La segunda consulta devuelve tambien tres unidades para el pseudoproducto.

Sobre la base de estos hechos, se puede afirmar que el error no está en las consultas, sino en los datos que tienes en la base.

¿Te quedó claro ahora?

Revisa los datos, verifica las relaciones, define correctamente las FK, depura los datos "basura" (datos que no cumplen las restricciones), y vuelve a probar las consultas.

[yeyowave]

Me parece muy bonito que me mandes una sancion quitandome 2 puntos por mi respuesta y no te sanciones a ti mismo por la respuesta dada sin venir a cuento, insultandome tu a mi directamente
tu mismo. yo ya tengo resuelto el problema..

[gnzsoloyo]

Tengo mis dudas de que tengas la solución...
Si realmente la tuvieses, la habrías mostrado, y no has respondido ninguna de las observaciones hechas.

Por cierto: La sanción, para que quede constancia pública, ya que no lo habíamos tratado, no es por la "respuesta" sino por los insultos, que si hubieses leído las Políticas de Foros del Web, sabrías que están prohibidos y son motivo de sanción.
Cualquier otra cosa sobre ese asunto, estoy a tu disposición por MP.

[yeyowave]

te voy a cerrar la boca de una vez con la solucion pubicada en este post.

http://www.forosdelweb.com/f86/consu...9/#post4226774

06/07/2012, 11:30







yeyowave





Fecha de Ingreso: febrero-2010

Mensajes: 113





Respuesta: consulta a 3 tablas

--------------------------------------------------------------------------------

compi una vez mas gracias por tu tiempo, he sacado la consulta ok

select cab.id_prod,pr.dispo,pr.descrip,cab.ultima_sol,det .cantidad from (avstock_cab cab left join productos pr on cab.id_prod = pr.id) inner join (select id_prod,count(id_prod) as cantidad from avstock_det av group by av.id_prod) det on det.id_prod = cab.id_prod group by cab.id_prod

[leonardo_josue]

Hola yeyowave:

También voy a meter mi cuchara a riesgo de que me pase lo que a gnzsoloyo .

En primer lugar, creo que te estás equivocando con el tono de tus respuestas hacia gnzsoloyo. Te aseguro que su intención siempre ha sido la de tratar de ayudar y basta con ver el número de mensajes y el karma que le han dado los usuarios para darse cuenta que es así, sin embargo, yo mismo tuve un "problema" similar por el tono que suele utilizar en sus post's. Sin embargo, creo en aquella ocasión pudimos aclarar la situación y darnos cuenta que todo fue un malentendido, como lo es ahora, desde mi punto de vista.

Lo que gnzsoloyo quería hacer notar de manera muy acertada por cierto es que CON LOS DATOS QUE PONES DE EJEMPLO, LA PRIMER CONSULTA QUE PONES DEBERÍA FUNCIONAR. Te pido dos minutos de tu tiempo para que cheques este script (que es básicamente el mismo que pone gnzsoloyo). En tu primer post dices esto:

pero ahora necesito mostrar el nombre y el stock del producto, como meto un left join? puesto que si lo pongo asi.

select stc.id_prod,pr.nombre,stc.f_actualizado,count(stod .id_prod) as esperan from avstock_cab stc left join productos pr on pr.id = stc.id_prod inner join avstock_det stod on stc.id_prod = stod.id_prod group by stc.id_prod

0, '', '20010230', 6 <---- no son 6, son 3 los que tienen que salir
1641, 'producto demo', '20120706', 4

Observa con atención este script, es la recreación de tus tablas de ejemplo CON LA MISMA CONSULTA QUE ESTÁS TRATANDO DE EJECUTAR:

Código MySQL:

mysql> SELECT * FROM productos;
+------+---------------+-------+
| id   | nombre        | stock |
+------+---------------+-------+
| 1641 | producto demo |    25 |
+------+---------------+-------+
1 row in set (0.01 sec)
 
mysql> SELECT * FROM avstock_cab;
+---------+------------+
| id_prod | ultima_sol |
+---------+------------+
|       0 | 20010230   |
|    1641 | 20120706   |
+---------+------------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM avstock_det;
+---------+------------------+
| id_prod | email            |
+---------+------------------+
|    1641 | [email protected] |
|    1641 | [email protected] |
|    1641 | [email protected] |
|    1641 | [email protected] |
|       0 | [email protected] |
|       0 | [email protected] |
|       0 | [email protected] |
+---------+------------------+
7 rows in set (0.00 sec)
 
mysql> SELECT
    ->   stc.id_prod,pr.nombre,
    ->   #stc.f_actualizado,
    ->   COUNT(stod .id_prod) AS esperan
    -> FROM avstock_cab stc
    -> LEFT JOIN productos pr ON pr.id = stc.id_prod
    -> INNER JOIN avstock_det stod ON stc.id_prod = stod.id_prod
    -> GROUP BY stc.id_prod;
+---------+---------------+---------+
| id_prod | nombre        | esperan |
+---------+---------------+---------+
|       0 | NULL          |       3 |
|    1641 | producto demo |       4 |
+---------+---------------+---------+
2 rows in set (0.00 sec)
 

Observa que el campo #stc.f_actualizado, está comentado porque no aparece en la estructura original de tus tablas. Con esta consulta el resultado ES EL QUE QUIERES OBTENER, en otras palabras NO ES POSIBLE DETERMINAR CÓMO ES QUE OBTUVISTE UN 6 COMO RESULTADO. Eso es lo que gnzsoloyo quería hacer notar, pero no estoy seguro de si entendiste lo que quería decir.

En cuanto al modelo de BD, creo que gnzsoloyo olvidó que en ocasiones no es posible modificarlo, pues no está en nuestras manos hacerlos INDEPENDIENTEMENTE DE QUE SEA UNA "BASURA" O NO, "Jerarquía mata intelecto" dijo alguna vez uno de mis maestros. Pero Creo que sería conveniente que si fuera el caso y pudieras modificar tus tablas lo hagas. Estoy de acuerdo en que puede haber una inconsistencia de datos por la manera en que tienes definidas tus tablas.

En fin, no quiero alargar más el tema, como comentario final te pediría nuevamente que tengas cuidado en la manera en que respondes un post, INDEPENDIENTEMENTE SI FUISTE OFENDIDO O NO. Un Gran Maestro, dijo alguna vez:

"Cuando alguien te de una bofetada en la mejilla, preséntale la otra"

Si seguimos la regla del OJO POR OJO, el mundo se llenará de tuertos.

Y gnzsoloyo, alguna vez te dije también que tus palabras en ocasiones suelen ser duras y ofensivas, aunque tu no lo pienses... creo que no está tampoco de más revisar un poco lo que escribimos no crees???

Saludos a todos
Leo.

[gnzsoloyo]

La verdad, si, es una muy buena idea releer las cosas antes de postearlas. Evita muchas veces problemas, y por suerte pudimos en aquel momento resolverlo en buenos términos.
En este caso, debo reconocer que se me pararon un poco los pelos de punta cuando vi los ejemplos, tal vez porque nunca había visto un caso de inconsistencia tan evidente y al que parecía no dar importancia o no evaluar correctamente.
Creo que lo que quise en realidad es hacerle ver el problema de una forma muy gráfica, pero la idea no cuajó.
También el problema es que tengo por política no modificar los posts que pongo una vez que han sido leídos, porque sino se prestan a confusiones y se pierde el hilo de las conversaciones.
Aún considerando eso, hay una cosa que quiero mencionar: Le ofrecí tres veces tratar el tema por MP, y hasta ahora no he recibido ninguna respuesta, privada o pública.
Eso si: no apliqué la sanción sin meditarlo. La puse porque correspondía dado el tono de la respuesta, y como cualquier otro moderador puedo removerla si @yeyowave se aviene a tratar el asunto en alguna forma, sea cual sea.

[leonardo_josue]

Pues ojalá yeyowave se anime a dar su punto de vista, ya sea como MP o en el foro. A final de cuentas todos estamos en el mismo barco y estoy seguro que habrá otras oportunidades para encontrarnos...

Saludos
Leo.