consulta relacionada con posibles registros inexistentes

[NSD]

buenas estube leyendo bastante de mysql y creo que lo quiero hacer no se puede (o por lo menos no encontre una solucion) el tema es asi:

tengo una tabla en una base de datos:

--------------
tabla1
--------------
ID | Nombre
--------------

y otra:

--------------
tabla2
--------------
ID | CantidadVendida
--------------

en la tabla1 guardo un id y el nombre de un producto y si se vende alguno guardo un registro en la tabla2 con el id del producto y la cantidad vendida.

quiero hacer un listado mostrando:

------------------------------
ID | Nombre | CantidadVendida
------------------------------

si hago una consulta con INNER JOIN solo me salen los que se vendieron solos (cosa logica ) al no haber ningun id que relacionar no muestra nada, pero quiero mostrar todos, y si no se vendieron mostrar 0 en cantidad vendida.

actualmente recorro la tabla1 con un while (PHP) y consulto la tabla2 con otro while dentro de este.

Mi pregunta:
¿Se puede relacionar de manera que si no existen los registros esa columna traiga un valor nulo o 0 pero que la seleccione igual? quiero recorrer una sola consulta no 2 una dentro de otra. espero haberme explicado...

[gnzsoloyo]

Es una consulta bastante simple, en realidad:

Código MySQL:

SELECT P.id, P.nombre, SUM(v.cantidadvendida) cantidadvendida
FROM producto P LEFT JOIN venta V ON P.id = V.id
GROUP BY P.id;
 

La puedes encontrar en cualquier tutorial sobre JOINs.

[NSD]

hola gnzsoloyo, gracias por responder pero con esa consulta obtengo el mismo resultado que con las demas, en la tabla 'venta' no estan los registros de los productos que no se vendieron, es decir ejemplo:

-------------
producto
-------------
ID | Nombre
--------------
1 | Panes
2 | Milanesas
3 | Figasas
4 | Quesos
--------------

-------------
venta
-------------
ID | CantidadVendida
-------------
1 | 5
3 | 2
-------------

y el resultado que quiero obtener es:

--------------
1 | Panes | 5
2 | Milanesas | 0/Null
3 | Figasas | 2
4 | Quesos | 0/Null
--------------

con tu consulta (y con las demas que intente y probe) obtengo este resultado:

--------------
1 | Panes | 5
3 | Figasas | 2
--------------

se te ocurre alguna forma de hacer eso?

[gnzsoloyo]

Pues obviamente algo de cómo la probaste no es correcto, porque si tomo los datos que tienes y creo las tablas, cargándolas con esos datos tendría:

Código MySQL:

mysql> DROP TABLE IF EXISTS PRODUCTO, VENTA;
Query OK, 0 rows affected (0.01 sec)
 
mysql> CREATE TABLE producto(
    ->     ID INT UNSIGNED PRIMARY KEY,
    ->     NOMBRE VARCHAR(50));
Query OK, 0 rows affected (0.01 sec)
 
mysql> CREATE TABLE venta(
    ->     IDVENTA INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     ID INT UNSIGNED,
    ->     CANTIDADVENDIDA INT UNSIGNED);
Query OK, 0 rows affected (0.01 sec)
 
mysql> INSERT INTO PRODUCTO
    -> VALUES
    ->     (1, 'Panes'),
    ->     (2, 'Milanesas'),
    ->     (3, 'Figasas'),
    ->     (4, 'Quesos');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO venta(ID, CantidadVendida)
    -> VALUES (1, 5), (3, 2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 

Ahora puedo ejecutar la consulta que te propuse, con un agregado para hacer que los NULL se transformen en ceros:

Código MySQL:

mysql> SELECT
    ->     P.id,
    ->     P.nombre,
    ->     IFNULL(SUM(v.cantidadvendida), 0) cantidadvendida
    -> FROM
    ->     producto P
    ->     LEFT JOIN venta V ON P.id = V.id
    -> GROUP BY P.id;
+----+-----------+-----------------+
| id | nombre    | cantidadvendida |
+----+-----------+-----------------+
|  1 | Panes     |               5 |
|  2 | Milanesas |               0 |
|  3 | Figasas   |               2 |
|  4 | Quesos    |               0 |
+----+-----------+-----------------+
4 rows in set (0.00 sec)
 

Y como para que veas que si le quito el IFNULL el resultado es el mismo:

Código MySQL:

mysql> SELECT
    ->     P.id,
    ->     P.nombre,
    ->     SUM(v.cantidadvendida) cantidadvendida
    -> FROM
    ->     producto P
    ->     LEFT JOIN venta V ON P.id = V.id
    -> GROUP BY P.id;
+----+-----------+-----------------+
| id | nombre    | cantidadvendida |
+----+-----------+-----------------+
|  1 | Panes     |               5 |
|  2 | Milanesas |            NULL |
|  3 | Figasas   |               2 |
|  4 | Quesos    |            NULL |
+----+-----------+-----------------+
4 rows in set (0.00 sec)
 

En resumen: El error se debe estar produciendo en la ejecución que haces tu, y no en los datos o la sentencia que te planteo.

[NSD]

En resumen: El error se debe estar produciendo en la ejecución que haces tu, y no en los datos o la sentencia que te planteo.

Que torpe soy! es verdad! acabo de revisar todo y tienes razon funciona a la perfeccion lo que me planteas, te pido disculpas y te agradesco por el tiempo que empleaste pero formule mal la pregunta, obvie un detalle que es el que hace que no me funcione esta solucion en la practica.

mi tabla 'venta' en realidad es asi:

-------------
venta
-------------
ID | CantidadVendida | IDPedido
-------------
1 | 5 | 1
3 | 2 | 1
2 | 5 | 2
4 | 2 | 2
-------------

lo que yo queria era realizar esa tabla que hace tu consulta pero para un IDPedido determinado:

Código MySQL:

SELECT
P.ID, P.Nombre, IFNULL(V.Cantidad, 0) Cantidad
FROM producto P
LEFT JOIN venta V ON P.ID = V.ID
WHERE V.IDPedido='2'
 

mi problema radica en el WHERE ya que estoy poniendo como condicion a un campo de un registro que no siempre existe por eso aun con tu sentencia me debuelve solo aquellos registros existentes...

se pude utilizar el IFNULL en el WHERE para hacerlo verdadero si el registro no existe?

[gnzsoloyo]

mi problema radica en el WHERE ya que estoy poniendo como condicion a un campo de un registro que no siempre existe por eso aun con tu sentencia me debuelve solo aquellos registros existentes...

La condición en el WHERE que necesitas para obtener un dato que puede ser NULL es muy simple. Lo que debes recordar es que NULL no es un dato, sino un estado, por lo que no sirve usar operadores lógicos (=, <, >, ETC). Para eso se usa el IS NULL o IS NOT NULL:

Código MySQL:

SELECT
    P.ID, 
    P.Nombre, 
    IFNULL(V.Cantidad, 0) Cantidad
FROM 
    producto P
    LEFT JOIN venta V ON P.ID = V.ID
WHERE 
    V.IDPedido='2' OR V.Cantidad IS NULL;
 

Aunque en el caso específico del ejemplo, el error es que estás filtrando el ID del pedido, que corresponde a la tabla de VENTA, y si existe en esa tabla, no puede ser NULL... es una imposibilidad lógica (a menos que puedas no ingresar el valor en ese campo cuando registrs una venta), por cuanto una venta no puede ser de cero productos.
En ese caso, si lo que quieres es obtener aquellos productos de los que no se realizaron ventas, la consulta sería:

Código MySQL:

SELECT
    P.ID, 
    P.Nombre, 
    IFNULL(V.Cantidad, 0) Cantidad
FROM 
    producto P
    LEFT JOIN venta V ON P.ID = V.ID
WHERE 
    V.Cantidad IS NULL;
 

Nota: El IFNULL se usa para evitar que aparezca en el resultado una columna con NULL. No tiene mucha utilidad en ningún otro caso, y por supuesto no lo tiene en el WHERE. Para eso se usa IS NULL o IS NOT NULL, como dije.

[NSD]

estube 'jugando' con tus codigos y probando las explicaciones a ver que tal me iva y llegue a la conclusion de que, en efecto, lo que quiero hacer es una "contradiccion logica" como dijiste vos y no puedo hacerlo desde mysql directamente.

le estoy pidiendo que me devuelva los ID, los Nombres de cada elemento de una tabla junto con la CantidadVendida almacenada en otra tabla que tenga ese mismo id y ademas pertenesca a un IDPedido determinado.

si la cantidad fuese 0 o nula el registro no existiria ya que no se guarda, por lo tanto no se va a cumplir la condicion de que pertenesca a un IDPedido determinado porque el registro no existe.

salvo que alla una forma de saber si el resultado de la interaccion es vacia no creo que se pueda.

si pongo:

V.IDPedido='2' OR V.IDPedido='2' IS NULL;

no obtengo ningun cambio porque al no encontrar un registro que cumpla la condicion no devuelve un null (nose si devuelve algo pero evidentemente un null no es ese algo) existe algun estado tipo "EMPLY" o algo similar?

perdon si la pregunta es una yeguada loca pero es lo unico que se me ocurre

[gnzsoloyo]

Vamos a ver.
SI lo que quieres es el listado de todos aquellos productos que no figuran en ningún pedido registrado en la tabla VENTA, esa es la respuesta de esta consulta:

Código MySQL:

SELECT
    P.ID, 
    P.Nombre, 
    IFNULL(V.Cantidad, 0) Cantidad
FROM 
    producto P
    LEFT JOIN venta V ON P.ID = V.ID
WHERE 
    P.Cantidad IS NULL;
 

O bien esta:

Código MySQL:

SELECT
    P.ID, 
    P.Nombre, 
    IFNULL(V.Cantidad, 0) Cantidad
FROM 
    producto P
    LEFT JOIN venta V ON P.ID = V.ID
WHERE 
    P.ID IS NULL;
 

Explico:
1) NULL es un estado de indeterminación, no un dato, como ya te dije.
A nivel de consultas, si intento relacionar un valor A1 de la tabla A con otro valor A2 de la tabla B, y no existe relación, me muestra una relación inexistente.
Como una no existencia es en álgebra relacional, un conjunto vacío, pero "vacío" en datos es otra cosa, lo que devuelve es una indeterminación o estado de nulidad, denominado NULL (por cierto, el tema de los NULL abarca un capítulo entero en el manual de referencia, deberías leerlo).
Entonces, NULL me muestra precisamente lo que es una "no relación".

2) LEFT JOIN devuelve todos los registros de la primera tabla (a la izquierda del LEFT JOIN), tengan o no relación con los registros de la tabla derecha. Y como dijimos que una "no relación" es un NULL, devuelve NULL en todas las columnas de la tabla derecha que no se relacionen con las de la izquierda.

¿Se entiende?

Resumiendo:
SI lo que quieres es que te devuelva los resultados de un determinado producto, aunque las ventas sean cero:

Código MySQL:

mysql> SELECT
    ->     P.ID,
    ->     P.Nombre,
    ->     IFNULL(V.CantidadVendida, 0) Cantidad
    -> FROM
    ->     producto P
    ->     LEFT JOIN venta V ON P.ID = V.ID
    -> WHERE
    ->     P.ID = '2';
+----+-----------+----------+
| ID | Nombre    | Cantidad |
+----+-----------+----------+
|  2 | Milanesas |        0 |
+----+-----------+----------+
1 row in set (0.00 sec)
 

Si quieres todos:

Código MySQL:

mysql> SELECT
    ->     P.ID,
    ->     P.Nombre,
    ->     IFNULL(V.CantidadVendida, 0) Cantidad
    -> FROM
    ->     producto P
    ->     LEFT JOIN venta V ON P.ID = V.ID
    -> WHERE
    ->     P.ID = '2' OR V.CantidadVendida IS NULL;
+----+-----------+----------+
| ID | Nombre    | Cantidad |
+----+-----------+----------+
|  2 | Milanesas |        0 |
|  4 | Quesos    |        0 |
+----+-----------+----------+
2 rows in set (0.03 sec)
 

Como puedes ver, sí se puede obtener datos aunque las ventas sean NULL. ¿Cuál es el secreto?
Simple:
- En el WHERE no puedes comparar valores contra los campos de la tabla derecha, sino contra la izquierda, para el caso de valores del campo usado como relación.
- Para determinar la "no relación" sólo debes preguntar si son NULL los campos de la tabla derecha, ya que los NULL de la izquierda no existen.
- Todo valor de la tabla derecha usado para ser comparado con algún parámetro, sólo aplicará con los registros que existan en la tabla derecha. Todos los demás son NULL.

[NSD]

(por cierto, el tema de los NULL abarca un capítulo entero en el manual de referencia, deberías leerlo).

creo que me saltie esa parte pero seguire tu consejo y en breve lo leere, no puedo creer que sea tan ignorante en este tema

En el WHERE no puedes comparar valores contra los campos de la tabla derecha, sino contra la izquierda, para el caso de valores del campo usado como relación.

es por eso que no me resultaba entonces, estaba haciendo justamente eso, por lo tanto no me funcionaba.

Busque en base a lo que me dijiste y ENCONTRE UNA SOLUCION!!!! LO LOGRE JEJE!!!!

no es esactamente lo que buscaba pero funciona y me trae los resultados que espero esta es la consulta:

Código MySQL:

SELECT P.ID , P.Nombre , (SELECT V.Cantidad FROM ventas V WHERE P.ID=V.ID AND V.IDPedido='2') Cantidad
FROM pedidos P
 

usando select anidados en lugar de los join pude y obtengo un dichoso NULL en aquellos productos que en un determinado pedido no se usaron y por lo tanto no hay registros.

o mejor aun

Código MySQL:

SELECT P.ID , P.Nombre , IFNULL((SELECT V.Cantidad FROM ventas V WHERE P.ID=V.ID AND V.IDPedido='2'),0) Cantidad
FROM pedidos P
 

me muestra un '0' si no existe el registro

nose si se podra optimizar esa consulta o asi queda tu que opinas?

por cierto gracias por tu tiempo y las explicaciones son muy claras, mañana leere el capitulo sobre estados NULL y algun otro sobre el lenguaje asi me desasno de algunas cosas XD

[gnzsoloyo]

Posiblemente funcione mejor así:

Código MySQL:

SELECT 
    P.ID , 
    P.Nombre , 
        IFNULL(V.Cantidad,0) Cantidad
FROM 
    pedidos P LEFT JOIN ventas V  ON P.ID=V.ID
WHERE 
    V.IDPedido='2';
 

Es conveniente evitar, en lo posible, poner una subconsulta en una columna del SELECT. Esas subconsultas se ejecutan una vez por cada egistro devuelto por el SELECT mayor, lo que consume la buena performace que pudas ganar de cualquier otra forma.

[NSD]

gracias por tu ayuda ya lo implemente y funciona perfecto