Consulta de cuatro tablas... INICIACION

[TheDave86]

Buenas, me estoy iniciando en la programacion para bases de datos, y se me esta haciendo un poco espeso el entender como hacer consultas de multiples tablas.

El tema es el siguiente, se plantea el ejercicio en el cual me piden que extraiga todos los subproductos de una base de datos con las siguientes tablas

pe_marcas (id_marca, name)
pe_productos(id_pro, title, marca_id, Descripcion )
pe_subproductos(id_subpro, producto_id, tipo_id, cantidad, PVR, DTO)
pe_tipos(id_tipo, descripcion)

Me piden que saque de cada subproducto el PVR, DTO, name, title y descripcion (la de pe_tipos)

teniendo en cuenta que la relacion es
id_marca<>marca_id,
id_pro<>producto_id,
tipo_id<>id_tipo.

El problema que yo tengo es que al pedirme varias cosas de tablas distintas no entiendo muy bien como se establece la relacion en el codigo, es decir, la relacion de las tablas la tengo clara, pero no entiendo al hacerlo en codigo.

La consulta que yo hago que ni por asomo funciona es esta:

$QueryITEMS="SELECT pe_subproductos.cantidad,
pe_subproductos.PVR,
pe_subproductos.DTO,
pe_productos.title,
pe_marcas.name,
pe_tipos.descripcion
WHERE
pe_subproductos.producto_id = pe_productos.id_pro AND
pe_subproductos.tipo_id = pe_tipos.id_tipo AND
pe_productos.marca_id = pe_marcas.marca_id";

[gnzsoloyo]

Código MySQL:

SELECT pe_subproductos.cantidad,
pe_subproductos.PVR,
pe_subproductos.DTO,
pe_productos.title,
pe_marcas.name,
pe_tipos.descripcion
WHERE
pe_subproductos.producto_id = pe_productos.id_pro AND
pe_subproductos.tipo_id = pe_tipos.id_tipo AND
pe_productos.marca_id = pe_marcas.marca_id;
 

Esto no va a funcionar jamás, porque no estás indicando el FROM, por lo que MySQL no sabe de qué tablas debe leer y cómo las tiene que leer.

Procura leer un poco más de SQL básico... Luego pregunta las dudas que aparezcan.

[TheDave86]

Iniciado por gnzsoloyo

Código MySQL:

SELECT pe_subproductos.cantidad,
pe_subproductos.PVR,
pe_subproductos.DTO,
pe_productos.title,
pe_marcas.name,
pe_tipos.descripcion
WHERE
pe_subproductos.producto_id = pe_productos.id_pro AND
pe_subproductos.tipo_id = pe_tipos.id_tipo AND
pe_productos.marca_id = pe_marcas.marca_id;
 

Esto no va a funcionar jamás, porque no estás indicando el FROM, por lo que MySQL no sabe de qué tablas debe leer y cómo las tiene que leer.

Procura leer un poco más de SQL básico... Luego pregunta las dudas que aparezcan.

disculpa, tengo la consulta hecha con el from, el fallo ha sido al copiar, lo he copiado para que aparezca en el foro por lineas y he olvidado copiar el from...

Quedaria asi:

Código SQL:

SELECT
pe_subproductos.cantidad,
pe_subproductos.PVR,
pe_subproductos.DTO,
pe_productos.title,
pe_marcas.name,
pe_tipos.descripcion
FROM
pe_subproductos,
pe_productos,
pe_marcas,
pe_tipos
WHERE
pe_subproductos.producto_id = pe_productos.id_pro AND
pe_subproductos.tipo_id = pe_tipos.id_tipo AND
pe_productos.marca_id = pe_marcas.marca_id
 

[TheDave86]

Nadie me puede echar una mano?

[gnzsoloyo]

En base de datos, el orden de los factores, si altera el producto. Esto significa que salvo en el uso de INNER JOIN, los resultados pueden variar de acuerdo a cuál sea la primera tabla en ser leída.
Como en tu caso deberías usar LEFT JOIN esto tiene aún más imoprtancia.
Tienes que iniciar con la tabla que contenga la lista completa de todo lo que puede encontrarse, luego en la sub lista, que sólo devolverá aquellos donde existan relacioens, y así sucesivamente, de lo mayor a lo menor.

Código MySQL:

SELECT
    P.title,
    PS.cantidad,
    PS.PVR,
    PS.DTO,
    PM.name,
    PT.descripcion
FROM
    pe_productos P 
    INNER JOIN pe_subproductos ON P.id_pro = producto_id
    INNER JOIN pe_tipos PT ON PS.tipo_id = PT.Tid_tipo
    INNER JOIN pe_marcas PM ON P.marca_id = PM.marca_id;
 

Esto otro devuelve todos los productos, tengan o no subproducto y marca, y los subproductos serán devueltos tengan o no tipo.

Código MySQL:

SELECT
    P.title,
    PS.cantidad,
    PS.PVR,
    PS.DTO,
    PM.name,
    PT.descripcion
FROM
    pe_productos P 
    LEFT JOIN pe_subproductos ON P.id_pro = producto_id
    LEFT JOIN pe_tipos PT ON PS.tipo_id = PT.Tid_tipo
    LEFT JOIN pe_marcas PM ON P.marca_id = PM.marca_id;
 

Si ninguna de las consultas devuelve registros, tienes problemas con los datos de las tablas...

[TheDave86]

Agradezco mucho tu ayuda, y has dado en el clavo en cuanto al problema, eso es justo lo que me han explicado en clase y justamente lo que me cuesta ver... En consultas sencillas no hay problemas, sobre todo si es de una sola tabla, pero en el caso en el que me piden datos especificos de distintas tablas es donde no se como usarlas correctamente... Lo del INNER JOIN si me lo habian explicado con esto, y es precisamente lo que deberia saber usar para hacer correctamente las relaciones... Lo que no conoci es el LEFT JOIN...

Bueno, voy a probar esas consultas y a intentar entenderlas, a ver si me van bien, a menos que mi DB este mal construida, claro.

Gracias, volvere a preguntar dudas...

[gnzsoloyo]

[TheDave86]

jeje...serias tan amable de aclararme la asociacion?

Código SQL:

FROM
pe_productos P
   INNER JOIN pe_subproductos ON P.id_pro = producto_id
   INNER JOIN pe_tipos PT ON PS.tipo_id = PT.Tid_tipo
 

Ahi me pierdo un poco.

[gnzsoloyo]

Perdón, la dejé incompleta:

Código MySQL:

SELECT
      P.title,
      PS.cantidad,
      PS.PVR,
      PS.DTO,
      PM.name,
      PT.descripcion
FROM
      pe_productos P 
      INNER JOIN pe_subproductos PS ON P.id_pro = PS.producto_id
      INNER JOIN pe_tipos PT ON PS.tipo_id = PT.Tid_tipo
      INNER JOIN pe_marcas PM ON P.marca_id = PM.marca_id;
 

[TheDave86]

Iniciado por gnzsoloyo

Perdón, la dejé incompleta:

Código MySQL:

FROM
      pe_productos P 
      INNER JOIN pe_subproductos PS ON P.id_pro = PS.producto_id
      INNER JOIN pe_tipos PT ON PS.tipo_id = PT.Tid_tipo
      INNER JOIN pe_marcas PM ON P.marca_id = PM.marca_id;
 

Si, cierto, pero sigo sin entender la asociacion, como la has hecho

[gnzsoloyo]

Es la misma que proponías en la tuya:

Producto<-Subproducto->Tipo
Producto->Marca

El INNER JOIN simplemente hace que devuelva los registros coincidentes, pero tienen que cumplirse todas las relaciones para que obtenga datos.
El LEFT JOIN devuelve todo lo que está en la tabla izquierda, tenga o no coincidencias con la tabla derecha. Como la tabla derecha puede obtener NULL en algun caso, esos NULL no obtendrán relaciones en la tercera tabla (Tipos de producto), sólo aquellos de la segunda que existan pueden devolver datos o null de la tercera.

[TheDave86]

ok, entendido, y en cuanto a las abreviaturas que has usado, no tengo muy claro que significan, porque pones pe_tipos y despues PT... O con, pe_subproductos y despues PS... no tengo muy claro si son abreviaturas que has hecho para acortar la escritura o realmente forman parte de la asociacion de tablas tal cual...no entiendo esa estructura..

INNER JOIN pe_tipos PT ON PS.tipo_id = PT.Tid_tipo

[gnzsoloyo]

Son ALIAS. Se usan para suplantar los nombres de bases, tablas o columnas en una consulta, simplificando la sintaxis y permitiendo una mayor legibilidad. La únia restricción es que no se pueden usar en el WHERE como referencia a una columna; si se peude usar el ALIAS de una base o tabla en el WHERE.
Es parte del estandar SQL desde sus inicios.

[TheDave86]

Como dice en el titulo del post y en el primero de los post, "me estoy inicando en la programacion para BD", ya lo puede tener SQL desde sus inicios que yo acabo de llegar. Lo siento.

Por otro lado, conocia los alias, pero segun a mi me los han eneseñado necesitan de la estructura:

"nombreoriginal AS nombreALIAS"

y al no reconocer esa estructura en tu codigo, no sabia con que identificarlo. Pero como dije, me estoy iniciando. Y ahora tambien se que no todos los RDBMS necesitan de esa estructura para nombrar ALIAS, en el caso de MySQL no es necesario poner AS.

De todas formas el estandar SQL, desde sus inicios, señala que debe mantener la palabra clave AS o en su defecto incluir el ALIAS entre comillas dobles. Para que otras RDBMS, como es el caso de ORACLE, no den errores con tal sentencia...

Agradezco en cualquier caso tu apunte, eso me hizo investigar mas acerca de esto.

Ahora bien, olvidandonos del problema planteado en el primer post, me gustaria poder entender el funcionamiento de las consultas sobre multiples tablas. Que es lo que realmente no entiendo todavia. Como podria alguien de manera sencilla indicarme como se realiza una consulta para sacar determinadas columnas de determinadas tablas.


Ejemplo:

Código:

TablaA[ idA, nombre, numero]
TablaB[ idB,id_TablaA, id_TablaC, ciudad, fecha, color]
TablaC[ idC, Modelo, posicion]

¿Como sacar el nombre, numero, ciudad y modelo de un determiado idB?¿ y de todos los idB?

No es realmente el resolver el problema lo que me interesa, si no entender como se resuelve, para poder resolver otros...

¿Alguien me puede ayudar con una pequeña expliacion? por favor.

[gnzsoloyo]

Y ahora tambien se que no todos los RDBMS necesitan de esa estructura para nombrar ALIAS, en el caso de MySQL no es necesario poner AS.

De todas formas el estandar SQL, desde sus inicios, señala que debe mantener la palabra clave AS o en su defecto incluir el ALIAS entre comillas dobles. Para que otras RDBMS, como es el caso de ORACLE, no den errores con tal sentencia...

En realidad, la obligatoriedad del AS se eliminó del estandar ANSI-SQL hace ya bastantes años. Tanto MySQL como otros DBMS conservan su uso sólo por compatibilidad de versiones antiguas, pero no es obligatorio ni siquiera en el estandar.
En otros DBMs, como Oracle, puede ser que versiones anteriores a la 8 puedan exigirlo, pero puedo asegurarte que a partir de Oracle 8 ya no es mandatorio, ya que yo mismo trabajo en base de datos para una empresa con ése, y ninguna consulta de ningún stored procedure, de ningún packaging los incluye... y no por ello fallan.
Los que si puede suceder también es que algún conector en especial requiera el uso del AS por cuestiones de forma soportada (el ODBC, por ejemplo, no soporta ciertas sintaxis que MySQL usa).

Volviendo a tu problema, tu duda no se entiende demasiado. Me explico:
Ese tipo de operaciones se resuelve usando INNER JOIN, tal y como te lo puse de ejemplo más arriba, ya que INNER JOIN devuelve todos los registros donde el valor en los campos del ON sea el mismo. Eso lo puedes ver usando dos tablas con mucha facilidad.
El único detalle y diferencia entre usar dos o usar más tablas es que debes razonar la secuencia de lectura y las relaciones:
- La primera tabla es la primera en ser leída, por tanto debe ser la que siempre devuelva la mayor parte de los registros donde haya mayores variaciones. En el caso de Personas, la lista de personas. En el caso de Facturas, la lista de encabezados de facturas, y así sucesivamente.
- El primer INNER JOIN devuelve una tabla resultado de las que se denomina tabla derivada, porque deriva de un JOIN. Es ese resultado como tabla virtual lo que el DBMS usa para hacer el segundo JOIN, no las tablas anteriores.
- El segundo JOIN se hace sobre la tabla resultado como un todo, aún cunado el alias del campo en el ON indique a una sola de ellas. Eso no quiere decir que esté haciendo el JOIN con la tabla pura anterior, porque esa ya no existe a nivel lógico, lo que se indica es que busque el campo que correspondía a esa tabla y que está en la derivada. Lo que existe es la tabla derivada.
- Todo JOIN posterior sigue la misma lógica: Se apoya sobre la tabla resultado de A INNER JOIN B INNER JOIN C como un todo, y sucesivamente los siguientes hacen lo mismo.

¿Se entiende la lógica de esto?

Hay algunos detalles a tener en cuenta:
- Si una relación del ON es opcional, es decir, puede que no todos los de la tabla inicial tengan relación con ello, no se debe usar INNER JOIN sino LEFT JOIN. Pero debes tener en cuenta que si tienes un JOIN posterior, éste no devolverá datos que correspondan a la tabla derivada donde esos datos sean NULL.
- Debes encadenar la secuencia de tablas de un modo correcto. Como el JOIN ya teniendo una dependencia secuencial, si uno de los JOIN intermedios devuelve menos de los que necesita uno posterior, los datos que podría devolver el siguiente pueden ser menos de los reales. Por eso digo siempre que en los JOIN, el orden de los factores si altera el producto.

Espero que esto se entienda

[TheDave86]

En primer lugar, muchas gracias por tu paciencia

En segundo lugar, ahora si que lo comprendi, eso es exactamente lo que iba buscando, una definicion "sencilla" de INNER JOIN sobre multiples tablas, y con la que me das lo entiendo perfectamente.

Por tanto Muchas gracias de nuevo.

Y por ultimo, por fin resuelto el tema, es preguntarte que diferencia existe entre las consultas sobre multiples tablas con INNER, y las sentencias sencillas. Obviamente la longitud de una sentencia sin INNER puede ser bastante mas extensa, pero en realidad, a parte de por resumir, no se que sentido y diferencia existe entre INNER y por ejemplo esta:

Código MySQL:

SELECT 
TA.nombre, TA.numero, TB.ciudad, TC.modelo 
FROM 
TablaA AS "TA", TablaB AS "TB", TablaC AS "TC" 
WHERE 
TA.idA=TB.id_TablaA AND TC.idC=TB.id_TablaC
 

Una vez comprendido el INNER y probado con mis tablas las consultas me doy cuenta que usando INNER y usando este tipo de consultas, obtengo el mismo resultado (aun constandome aun elaborar las sentencias INNER ya que las acabo de entender)

Entonces, si hacen exactamente lo mismo, quiza de manera mas resumida, que ventaja puedo encontrar en el INNER...Merece mi esfuerzo el comprender a fondo la sentecias INNER?

[gnzsoloyo]

Hay una gran diferencia entre usar INNER JOIN para realizar una consulta, y poner las condicioens en el ON: La performance.
Cuando usas INNER | LEFT | RIGH JOIN, los registros coincidentes se van seleccionado a medida que se leen las tablas, por lo que el proceso es bastante rápido. Además, el FROM es optimizable por el parser, lo que quiere decir que puede usar diferetes algoritmos internos según lo que determine, y también según los resultados obtenidos estadísticamente en el uso de otras consultas.
No te olvides que en un DBMS hay inteligencia incorporada para tomar decisiones según resultados anterores. Por eso dos consultas estructuralmente idénticas pueden tener tiempo de ejecución distinta.
En cambio, cuando usas las condiciones en el WHERE, MySQL no puede hacer optimizaciones antes de tener todas las tablas, porque el WHERE se aplica sobre los resultados de la lectura, lo que en realidad quiere decir que debe levantar todos los registros, los coincidentes y los no coincidentes, antes de verificar cuáles cumplen con las condiciones. Esto es sencillamente porque las condicioens del WHER se aplican sore la tabla resultado de la junta... ¿Se entiende?

Cuando pruebas la performance con tablas pequeñas, el impacto de la forma de usar las condiciones no es muy visible. La rapidez del procesamiento no permite ver grandes diferencias. Pero cuando tienes muchos datos (1.000.000 registros por tabla o más), la diferencia se hace notoria.

Sintetizando:

1) Evita en lo posible usar la coma para crear el JOIN.
2) Usa INNER | LEFT | RIGH JOIN según lo necesites.
3) No leas más columnas de las que usarás ("SELECT *" es la peor forma de consulta). Ocupan espacio en memoria y si no usas todos los datos, traes basura.
4) Crea índices para las consultas más habituales. No para todas. Usan disco y puedes afectar la performance de los INSERT / UPDATE.