Como enlazar multiples tablas. Optimizar consulta

[alyciashape]

Es decir, se que hay diferentes formas, pero me encuentro conque tengo que enlazar muchas tablas y es un auténtico caos. Para simplificarme la vida lo he intentado hacer a traves de Access y un driver ODBC aver si así, pero es una consulta muy compleja (o puede que mal hecha) y no ejecutando la consulta se queda la vida eterna intentando realizarla hasta que me canso y tengo que colgarlo.

Esta es la consulta, tampoco creo que sea para tanto, lo único malo serían tantos enlaces. Hay un par de tablas que no se usan porque aun me quedan algunos datos por poner (con sumas de precios para mas inri) pero es que ya si no saco ni esto.

Se que se pueden hacer las relaciones de varias formas. Yo suelo usar esta, aunque se alguna más:
· WHERE tabla1 = tabla2 USING(clave)

Me suena que hay alguna forma de enlazar multiples tablas que usen la misma clave pero no estoy seguro de como es. Aquí la consulta que me crea el access automáticamente tras decirle que campos quiero consultar. La base de datos es un caos no la diseñé yo y está fatal hecha, así que tampoco ayuda.

Código MySQL:

SELECT clientes.clacli AS CÓDIGO, articulo.nombre AS NOMBRE, clientes.direccion AS DIRECCIÓN, clientes.localidad AS LOCALIDAD, clientes.telefono AS TELÉFONO, clientes.contacto AS CONTACTO, clientes.provincia AS PROVINCIA, tarifa.nombre AS TARIFA
FROM articulo, catart, factural, tarifa, (factura INNER JOIN clientes ON factura.clacli=clientes.clacli) INNER JOIN tiposcli ON clientes.clatip=tiposcli.clatip
WHERE articulo.clacat=21 And clientes.clatip=2;
 

Como veis los enlaces entre tablas son excesivamente complejos, me imagino que por eso se muere.

He intentado hacerlo a mano pero no logro seguir un orden lógico.
Si enlazo articulo con catart (categoria articulo) luego esta categoria con cual la voy a enlazar? no se si me explico. Hay que seguir un orden no?

Este es el meollo:
TABLA -> Su clave y las que llama

articulo
claart(pk)
clacat (fk de categoria articulo)


factura
clafac(pk)


factural
clafacl (pk)
clafact (fk de factura)

tarifa
clatar (pk)

clientes
clacli (pk)
clatar (fk)
clatip (fk)

tiposcli
clatip (pk)

catart
clacat (pk)

Como veis los nombres de las tablas e ids tampoco ayudan mucho. Un auténtico lío. No pido que me hagais la relación sino la sintaxis para poder enlazar varias tablas porque no encuentro un orden en el que hacerlo.

Extra: También si alguno de vosotros sabe me gustaría saber si en una subconsulta puedo hacer referencia a alguna de las claves de la consulta normal. Algo así:
SELECT campos, (SELECT otras_cosas FROM tablas WHERE id_de_la_otra_consulta) FROM tablas

Es decir que para cada id de la consulta principal me busque en la subconsulta.

[Heimish2000]

Usa JOINS para unir todas las tablas y en el where pon la condición más restrictiva primero.

[alyciashape]

Gracias al final lo he conseguido separando las consultas en 2. Como puedo unir ambas?
Pero uniéndolas de forma que la tabla a busque su correspondiente en la tabla B.

Es decir, tengo estas 2 consultas:

Código MySQL:

SELECT c.clacli, SUM((fl.precio-((fl.precio*fl.dto)/100))*fl.cantidad) AS 'TOTAL 2012' 
FROM clientes c, factura f, factural fl, articulo a 
WHERE c.clacli=f.clacli AND f.clafac=fl.clafac  AND fl.claart=a.claart  AND a.clacat=21 AND year(f.fecha)=2012 
GROUP BY c.clacli
 

Por otro lado

Código MySQL:

SELECT c2.clacli AS 'CÓDIGO', c2.nombre AS 'NOMBRE', c2.direccion AS 'DIRECCIÓN', c2.localidad AS 'LOCALIDAD', c2.telefono AS 'TELÉFONO', c2.contacto AS 'CONTACTO', c2.provincia AS 'PROVINCIA', tar.nombre AS 'TARIFA'
FROM clientes c2, tiposcli tipos, codpais cod, tarifa tar
WHERE c2.clatar=tar.clatar
AND c2.clatip=tipos.clatip
AND c2.clapai=cod.clapai
GROUP BY c2.clacli
 

Como puedo hacer que salgan los datos de ambas tablas cuando coincidan los clacli¿? Creo que se puede pero nunca lo hice y estoy buscando y no encuentro nada al respecto.

[Heimish2000]

Con un JOIN

Código SQL:

SELECT *
FROM (SELECT c.clacli, SUM((fl.precio-((fl.precio*fl.dto)/100))*fl.cantidad) AS 'TOTAL 2012'
FROM clientes c, factura f, factural fl, articulo a
WHERE c.clacli=f.clacli AND f.clafac=fl.clafac AND fl.claart=a.claart AND a.clacat=21 AND YEAR(f.fecha)=2012
GROUP BY c.clacli) AS A
INNER JOIN (
SELECT c2.clacli AS 'CÓDIGO', c2.nombre AS 'NOMBRE', c2.direccion AS 'DIRECCIÓN', c2.localidad AS 'LOCALIDAD', c2.telefono AS 'TELÉFONO', c2.contacto AS 'CONTACTO', c2.provincia AS 'PROVINCIA', tar.nombre AS 'TARIFA'
FROM clientes c2, tiposcli tipos, codpais cod, tarifa tar
WHERE c2.clatar=tar.clatar
AND c2.clatip=tipos.clatip
AND c2.clapai=cod.clapai
GROUP BY c2.clacli) AS B ON
A.clacli = B.CODIGO
 

[alyciashape]

Muchas gracias Heimish!

[alyciashape]

Bueno tras mil y un problemas al final he tenido que hacerlo en una única consulta. Así:

Código MySQL:

SELECT c.clacli AS CODIGO, c.nombre AS NOMBRE, c.direccion AS DIRECCION, c.localidad AS LOCALIDAD, c.telefono AS TELEFONO, c.contacto AS CONTACTO, c.provincia AS PROVINCIA, tar.nombre AS TARIFA, 
                ROUND(SUM((fl.precio-((fl.precio*fl.dto)/100))*fl.cantidad), 2) AS TOTAL
                FROM tiposcli tipos, tarifa tar, codpais cod, clientes c, factura f, factural fl, articulo a
                WHERE tipos.clatip=c.clatip
                AND tar.clatar=c.clatar
                AND cod.clapai=c.clapai
                AND c.clacli=f.clacli
                AND fl.clafac=f.clafac
                AND a.claart=fl.claart
                AND a.clacat=21 
                AND c.clatip=2                
                $filtro                 
                GROUP BY c.clacli
 

La cuestión es que ahora me dicen que deberían salir también los que no tienen resultados. Es decir, un LEFT JOIN. Pero no veo forma humana de convertir eso a LEFT JOIN.

[gnzsoloyo]

Por favor, alyciashape, cuando pongas código SQL, haz el favor de usar el etiquetado de código correcto.
MySQL no es PHP.

[alyciashape]

Claro. Es que ya lo he intentado y no veo "SQL" por ningún lado. Sólo veo HTML, CODE y PHP por eso entre esos me decidí por PHP.

[gnzsoloyo]

¿Estás segura de lo que dices?
Porque este es el contenido real:

[alyciashape]

Ah vaya, Highlight pensé que era para poner un fondo a la letra, en muchos programas es eso sino me equivoco ni se me pasó por la cabeza. Lo que hacía era ir a avanzado para poder poner las etiquetas pinchando en los botones correspondientes, jaja. Para otra ya sé.

De hecho pongámoslo en práctica. He llegado a esto pero algo tiene que estar mal porque me muestra algunos más pero no todos los que no tienen facturas para el citado año:

Código SQL:

SELECT c.clacli AS CODIGO, c.nombre AS NOMBRE, c.direccion AS DIRECCION, c.localidad AS LOCALIDAD, c.telefono AS TELEFONO, c.contacto AS CONTACTO, c.provincia AS PROVINCIA, tar.nombre AS TARIFA, 
ROUND(SUM((fl.precio-((fl.precio*fl.dto)/100))*fl.cantidad), 2) AS TOTAL            
FROM tiposcli tipos, tarifa tar, codpais cod LEFT JOIN clientes c ON cod.clapai=c.clapai LEFT JOIN factura f ON c.clacli=f.clacli LEFT JOIN factural fl ON f.clafac=fl.clafac LEFT JOIN articulo a ON fl.claart=a.claart
WHERE tipos.clatip=c.clatip
AND tar.clatar=c.clatar                             
AND a.clacat=21
AND c.clatip=2
$filtro                 
GROUP BY c.clacli
ORDER BY TOTAL
 

Es incorrecto mezclar left join con los join normales =?

Es que esos 2 campos que quedan, además de que siempre estan enlazados así que supongo que daría igual, no veo forma de meterlos ahí en medio enlazando con clientes.

[gnzsoloyo]

Es incorrecto mezclar left join con los join normales =?

No, pero tienes que tener cuidado en la lógica de resolución de ambos.
Un left join te devovlerá nulos donde no haya coincidencias, por lo que todo INNER JOIN que se apoye en los datos resultados de la tabla izquierda devolverá nulos en ese caso.
Hay que analizar con cuidado los resultados que se obtienen y las relaciones que se establecen.
Por otro lado, en ciertas ocasiones, cuando se realizan JOINs en forma de estrella, pueden generarse productos cartesianos sin que te des cuenta.
Combinar múltiples tablas no es una tarea sencilla.

[alyciashape]

Si, puedo confirmar que es una difícil tarea.

He intentado simplificar que es lo que me suele funcionar cuando no me libro de algún problema, pero aún así no logro lo que quiero. Partiendo de esta simple consulta, me muestra sólo los clientes que TIENEN facturas pero yo quiero ver también los que no compraron nada este año.

Código SQL:

SELECT clientes.clacli, nombre, SUM(factural.precio) AS total 
FROM clientes 
LEFT JOIN factura ON clientes.clacli=factura.clacli 
LEFT JOIN factural ON factura.clafac=factural.clafac 
WHERE localidad='VIGO' AND YEAR(factura.fecha)=2012 
GROUP BY clientes.clacli 
ORDER BY total
 

Puedo asegurar que hay clientes que no compraron NADA pero sólo salen los que si compraron. Y creo que el orden está bien y la sintaxis y todo. Acaso puse algo mal?

[gnzsoloyo]

Empecemos por esto:

Código MySQL:

SELECT clientes.clacli, nombre, COUNT(factura.fecha) Facturas
FROM clientes 
    LEFT JOIN factura ON clientes.clacli=factura.clacli 
WHERE localidad='VIGO' AND YEAR(factura.fecha)=2012 
GROUP BY clientes.clacli 
ORDER BY total
 

¿Que te devuelve y cuántos registros?

[alyciashape]

Pues me devuelve no se, unos 100 registros y todos tienen como mínimo, 1 factura. Digamos que unos cien.

Esto me devuelve unos 1050 registros
SELECT count(nombre) FROM clientes WHERE localidad='VIGO'

¿Así que en la otra consulta deberían salir los 900 restantes no?

[gnzsoloyo]

Los 900 restantes no tienen la fecha del año 2012.

[alyciashape]

Pero 2012 es la fecha de emision de la factura no del cliente. No tendrían que salir?

Tiene que aver una forma de sacar los clientes que tengan y no tengan facturas en 2012.

[gnzsoloyo]

¿QUé contiene el campo CLACLI en ambas tablas?

[alyciashape]

clacli es el identificador del cliente (clave cliente) si, la bd esta tiene unos nombres horribles, especialmente para mi que suelo poner nombres completos como id_cliente.

Y si a partir de esto:

Código SQL:

SELECT clientes.clacli, nombre, SUM(factural.precio) AS total 
FROM clientes 
LEFT JOIN factura ON clientes.clacli=factura.clacli 
LEFT JOIN factural ON factura.clafac=factural.clafac 
WHERE localidad='VIGO' AND YEAR(factura.fecha)=2012 
GROUP BY clientes.clacli 
ORDER BY total
 

De alguna forma hacer otro select que me saque los clientes que NO estén en ese select. Aunque no sé la sintaxis.

Pero aún así yo creo que lo de antes tendría que funcionar.

[gnzsoloyo]

Si entiendo correctamente, FACTURA es el encabezado de la factura, con los datos de la emisión de la misma, y FACTURAL, son los items correspondientes
Ahora bien, si eso es correcto, entonces la consulta así debería funcionar:

Código MySQL:

SELECT C.clacli, C.nombre, SUM(FL.precio) total 
FROM clientes C LEFT JOIN factura F ON C.clacli = C.clacli 
     INNER JOIN factural FL ON F.clafac = FL.clafac 
WHERE localidad='VIGO' AND YEAR(F.fecha)=2012 
     OR F.clafac IS NULL
GROUP BY C.clacli 
ORDER BY total
 

En el segundo JOIN debe ponerse INNER JOIN y no LEFT JOIN, porque puede haber clientes sin factura, pero jamás puede existir una factura que no tenga items.

[alyciashape]

Lo tengo que probar aún pero tiene toda la pinta de ser lo que necesitaba. Muchas gracias!