tablas - sql tabla muchos a muchos

Filtre una consulta de uno a muchos solicitando que todos cumplan los criterios

Question

Imagina las siguientes tablas:

crear cuadros de tabla (id int, nombre de texto, ...);

crear table thingsinboxes (id int, box_id int, thing enum (''apple'', ''banana'', ''orange'');

Y las tablas se ven así:

Boxes: id | name 1 | orangesOnly 2 | orangesOnly2 3 | orangesBananas 4 | misc thingsinboxes: id | box_id | thing 1 | 1 | orange 2 | 1 | orange 3 | 2 | orange 4 | 3 | orange 5 | 3 | banana 6 | 4 | orange 7 | 4 | apple 8 | 4 | banana

¿Cómo selecciono los cuadros que contienen al menos una naranja y nada que no sea naranja?

¿Cómo funciona esta escala, suponiendo que tengo varios cientos de miles de cajas y posiblemente un millón de cosas en cajas?

Me gustaría mantener todo esto en SQL si es posible, en lugar de procesar posteriormente el conjunto de resultados con un script.

Estoy usando tanto postgres como mysql, por lo que las subconsultas probablemente sean malas, dado que mysql no optimiza las subconsultas (previas a la versión 6, de todos modos).

Answer 1

Creo que la consulta de Bill Karwin está bien, sin embargo, si una proporción relativamente pequeña de cajas contiene naranjas, debería poder acelerar las cosas usando un índice en el campo de thing :

SELECT b.*
FROM boxes b JOIN thingsinboxes t1 ON (b.id = t1.box_id)
WHERE t1.thing = ''orange''
AND NOT EXISTS (
    SELECT 1
    FROM thingsinboxes t2
    WHERE t2.box_id = b.id
    AND t2.thing <> ''orange''
)
GROUP BY t1.box_id

La subconsulta WHERE NOT EXISTS solo se ejecutará una vez por naranja, por lo que no es demasiado caro, siempre que no haya muchas naranjas.

Answer 2

SELECT b.*
FROM boxes b JOIN thingsinboxes t ON (b.id = t.box_id)
GROUP BY b.id
HAVING COUNT(DISTINCT t.thing) = 1 AND SUM(t.thing = ''orange'') > 0;

Aquí hay otra solución que no usa GROUP BY:

SELECT DISTINCT b.*
FROM boxes b
  JOIN thingsinboxes t1 
    ON (b.id = t1.box_id AND t1.thing = ''orange'')
  LEFT OUTER JOIN thingsinboxes t2 
    ON (b.id = t2.box_id AND t2.thing != ''orange'')
WHERE t2.box_id IS NULL;

Como siempre, antes de llegar a conclusiones sobre la escalabilidad o el rendimiento de una consulta, debe intentarlo con un conjunto de datos realista y medir el rendimiento.