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