sql - ¿Cómo se hacen muchas combinaciones externas de tablas?

join many-to-many

Question

Tengo 3 tablas, foo, foo2bar y barra. foo2bar es un mapa de muchos a muchos entre foo y bar. Aquí están los contenidos.

select * from foo
+------+
| fid  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

select * from foo2bar
+------+------+
| fid  | bid  |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    1 |
|    2 |    3 |
|    4 |    4 |
+------+------+

select * from bar
+------+-------+------+
| bid  | value | zid  |
+------+-------+------+
|    1 |     2 |   10 |
|    2 |     4 |   20 |
|    3 |     8 |   30 |
|    4 |    42 |   30 |
+------+-------+------+

Lo que quiero solicitar es: "Dame una lista de todos los archivos y valores con un factor de 30".

Espero una respuesta para todos los fids, por lo que el resultado sería:

+------+--------+
| fid  | value  |
+------+--------+
|    1 |   null |
|    2 |      8 |
|    3 |   null |
|    4 |     42 |
+------+--------+

Answer 1

SELECT * FROM foo
  LEFT OUTER JOIN (foo2bar JOIN bar ON (foo2bar.bid = bar.bid AND zid = 30))
  USING (fid);

Probado en MySQL 5.0.51.

Esta no es una subconsulta, solo usa paréntesis para especificar la precedencia de las uniones.

Answer 2

FWIW, la pregunta no es realmente acerca de un muchos-a-muchos: esto podría hacerse simplemente como una unión.

Un verdadero many-to-many en SQL es una CROSS JOIN

Answer 3

Si no está obteniendo una fila para fid = 3, entonces su servidor está roto.

Este código debería hacer lo que yo creo que quieres:

SELECT
    F.fid,
    SQ.value
FROM
    dbo.Foo F
LEFT OUTER JOIN
     (
     SELECT F2B.fid, B.value
     FROM dbo.Bar B
     INNER JOIN dbo.Foo2Bar F2B ON F2B.bid = B.bid WHERE B.zid = 30
     ) SQ ON SQ.fid = F.fid

Tenga en cuenta que es posible recuperar dos valores para un fid si está relacionado con dos barras con un valor de 30.

Answer 4

SELECT * FROM
        foo LEFT JOIN
        (
        Foo2bar JOIN bar
             ON foo2bar.bid = bar.bid AND zid = 30
        )
        ON foo.fid = foo2bar.fid;

no probado

Answer 5

Para resolverlo, puede comenzar con su selección. No incluya columnas que no quiera ver en última instancia.

SELECT foo.fid, bar.value

Entonces podemos hacer la cláusula WHERE, que puede ver es exactamente como la expresaste.

SELECT foo.fid, bar.value  
WHERE bar.zid = 30

Ahora es la parte difícil de conectar cosas juntas para nuestra cláusula FROM, usando LEFT JOINs porque queremos ver cada fid, si hay coincidencias intermedias o no:

SELECT foo.fid, bar.value  
FROM foo  
LEFT JOIN foo2bar ON foo.fid = foo2bar.fid  
LEFT JOIN bar ON foo2bar.bid = bar.bid  
WHERE bar.zid = 30
OR bar.zid IS NULL